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.
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!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on 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.
- Things SQL Server vNext Should Address: How Did I Do?
- Things SQL Server vNext Should Address: Add Lock Pages In Memory To Setup Options
- Things SQL Server vNext Should Address: Add Cost Threshold For Parallelism To Setup Options
- Changes Coming To SQL Server’s STRING_SPLIT Function: Optional Ordinal Position