Datediff Efficiency

Question

I have a few days variable that I want to compare with the datetime (senddate) column.

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 everything that is older than 10 days should be deleted, we have an index in the sendDate column, but the speed is much slower, I know that the left side should not have calculations for performance reasons, but what is the best way otherwise case solve this problem?

Answer 1

Expression

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

will not be able to use the index in the senddate column due to the function on the LHS on senddate

To make a WHERE "SARGable" (that is, use an index), change it to an equivalent condition:

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

[Thanks @Krystian Lieber for pointing out the wrong condition].