Datediff performance

Question

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

I'm currently doing this:

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

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

Anything that is older than 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?

Answer 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 column 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 ].