We recently dove into sargability and strings. In today’s post, let’s take a look at an example of sargability and dates.
Created a Monster
We’ll use the StackOverflow2013 database for this example. Let’s say we want to return the users created in 2013. One way to return this data is to use the YEAR() function to pull out the desired year for our query:
SELECT DisplayName, CreationDate FROM Users WHERE YEAR(CreationDate) = '2013' ORDER BY CreationDate; GO
This works but requires scanning the clustered index. We’ll follow the same steps as the last blog post and create an index to try improving the query:
CREATE INDEX CreationDateIncludeDisplayName ON Users (CreationDate) INCLUDE (DisplayName); GO
If we run our query again:
We scan our new index instead of the clustered index but the query is still not getting a seek. We can’t seek because our query isn’t sargable.
Between Stupidity and Genius
We’ll make a change in our WHERE clause to get rid of the YEAR() function. What would happen if we use BETWEEN instead:
SELECT DisplayName, CreationDate FROM Users WHERE CreationDate BETWEEN '2013-01-01' AND '2014-01-01' ORDER BY CreationDate; GO
There’s the seek for our new sargable query and the seek plan has a lower cost:
Do’s and Don’ts
The common sargable operators are:
=, >, >=, <, <=, IN, BETWEEN, and LIKE conditions like ‘STRING%’
On the other side, common non-sargable operators are:
<>, !=, NOT EXISTS, NOT IN, OR, and LIKE conditions like ‘%STRING’
Functions do not automatically make a query non-sargable. DATEADD(), DATEDIFF(), and YEAR() which was shown above are not sargable; however, using CONVERT() can be fine.
Keep sargability in mind if you’re troubleshooting a performance issue or developing new queries.
Thanks for reading!