DATEADD not producing a SARGable expectation of an index seek

I have a basic [UserActivity] table that captures an ActivityTypeId per UserId and the ActivityDate at which the Activity occurred.

I am writing a query/stored procedure that allows for input of the @UserId, @ForTypeId, as well as the @DurationInterval and @DurationIncrement to dynamically return results based on N number of seconds/minutes/hours/days/months/years. Given that the datepart argument within DATEADD/DATEDIFF does not allow parameters, I had to revert to a bit of trickery in order to get the desired results within the WHERE clause.

Initially I wrote the query using DATEDIFF, but immediately after writing and taking a peek at the execution plan, I remembered that it is not a SARGable function (along with the fact the precision levels could offer for some dates falling off in a Leap Year). So, I re-wrote the query to utilize DATEPART thinking that I would hit an index seek instead of an index scan and generally perform better.

Unfortunately, I've found that writing the query as DATEADD offers up the same results: an index scan is occurring, and query optimizer is not leveraging the non-clustered index against [ActivityDate].

I read Aaron Bertrand's blog post, "Performance Surprises and Assumptions: DATEADD", and implemented the changes he described to CONVERT the DATEADD portion into the equivalent datetime2 column definition due to weird trickery involved with datetime2. However, the issue was still present even after doing so.

To better illustrate the scenario, here is a comparable table definition.

```
DROP TABLE IF EXISTS [dbo].[UserActivity]
IF OBJECT_ID('[dbo].[UserActivity]', 'U') IS NULL
BEGIN
CREATE TABLE [dbo].[UserActivity] (
[UserId] [int] NOT NULL
,[UserActivityId] [bigint] IDENTITY(1,1) NOT NULL
,[ActivityTypeId] [tinyint] NOT NULL
,[ActivityDate] datetime2 NOT NULL CONSTRAINT [DF_UserActivity_ActivityDate] DEFAULT GETDATE()
,CONSTRAINT [PK_UserActiv

The OR condition evaluates at compile-time, rather than at runtime, which means that your WHERE condition does not generate a seek.

And just to clean up the code, I refactored out your CONVERT to make the code a bit more readable.

I would try changing the WHERE clause to:

UA.[ActivityDate]>CONVERT(datetime2(0), (CASE
    WHEN @DurationInterval IN ('year', 'yy', 'yyyy') THEN DATEADD(year, -@DurationIncrement, GETDATE())
    WHEN @DurationInterval IN ('month', 'mm', 'm')   THEN DATEADD(month, -@DurationIncrement, GETDATE())
    WHEN ...
    END))

I don't have access to an environment where I can verify this, but please let me know if it works out.

StackExchange Database Administrators Q#202419, answer score: 9