Things SQL Server vNext Should Address: Making Date Math SARGable

Whiffle Ball


How you do date math in a where clause matters, because wrapping a column in any sort of expression can really hurt your query performance.

The thing is that most rewrites are pretty simple, as long as there aren’t two columns fed into it.

For example, there’s not much the optimizer could do about this:

WHERE DATEDIFF(DAY, u.CreationDate, u.LastAccessDate) > 1

But that’s okay, because if you do that you deserve exactly what you get.

Computed columns exist for a reason. Use them.

Whaffle House


Where things get a bit easier is for simpler use cases where constant folding and expression matching can be used to flip predicates around a little bit.

It’s just a little bit of pattern recognition, which the optimizer already does to make trees and apply rules, etc.

CREATE INDEX p ON dbo.Posts(CreationDate);

There’s a huge difference between these two query plans:

SELECT 
    c = COUNT_BIG(*)
FROM dbo.Posts AS p
WHERE DATEADD(MONTH, -1, p.CreationDate) >= '20191101'
GO 

SELECT 
    c = COUNT_BIG(*)
FROM dbo.Posts AS p
WHERE p.CreationDate >= DATEADD(MONTH, 1, '20191101');
GO
hand rub

Thanks for reading!



Leave a Reply

Your email address will not be published. Required fields are marked *