SARGability and Date Functions

Erik Darling shows why you don’t want to use YEAR() or MONTH() in the WHERE clause when querying a large table:

If you’ve been query tuning for a while, you probably know about SARGability, and that wrapping columns in functions is generally a bad idea.

But just like there are slightly different rules for CAST and CONVERT with dates, the repercussions of the function also vary.

The examples I’m going to look at are for YEAR() and MONTH().

Read the whole thing. Maybe “go to brunch” in the middle of it for maximum effect.

Related Posts

Finding Gaps in Dates

Jason Brimhall shows how you can find gaps in your data: This method is the much maligned recursive CTE method. In my testing it runs consistently faster with a lower memory grant but does cause a bit more IO to be performed. Some trade-off to be considered there. Both queries are returning the desired data-set […]

Read More

Adaptive Joins and Index Width

Erik Darling wants to pump your indexes up: Now, there’s an Extended Event that… Used to work. These days it just stares blankly at me. But since I’ve worked with this before, I know the problem. It’s that Key Lookup — I’ll explain more in a minute. Adaptive joins won’t do all the work for you, […]

Read More

Categories

April 2019
MTWTFSS
« Mar May »
1234567
891011121314
15161718192021
22232425262728
2930