Datediff performance

问题:

I have a number of days variable which I want to compare against a datetime column (senddate) .

Im currently doing this :

DECLARE @RunDate datetime = '2013-01-01' 
DECALRE @CalculationInterval int = 10

DELETE
FROM TableA
WHERE datediff(dd, senddate, @RunDate) > @CalculationInterval 

So basically anything that is older then 10 days should get deleted, we have Index on sendDate column but still the speed is much slower, I know the left side should not have calculation for performance reasons, but what is the optimal way of otherwise solving this issue?

回答1:

The expression

WHERE datediff(dd, senddate, @RunDate) > @CalculationInterval 

won't be able to use an index on the senddate column, because of the function on the LHS on senddate

In order to make the WHERE clause 'SARGable' (i.e. able to use an index), change to the equivalent condition:

WHERE senddate < dateadd(dd, -@CalculationInterval, @RunDate)

[Thanks to @Krystian Lieber, for pointing out incorrect condition ].