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

Thanks for reading!

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.