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!
Chad Callihan 
There is a slight error in your query using the BETWEEN operator. The BETWEEN operator includes both start and end endpoints. As a result, the query as written will include data for 2014-01-01. To fix, the end date should be 2013-12-31.
LikeLike
Thanks for the comment! I’m should have included time along with the dates to make the example more clear. The results can get confusing since SQL Server takes BETWEEN and converts that to CreationDate >= @1 AND CreationDate <= @2 in the plan. I'm going to do another blog post with more examples.
LikeLike
It depends on whether CreationDate datatype is date or datetime (datetime, datetime2, datetimeoffset). Based on the comparison value being a date, I assumed CreationDate was a date. This is also a way implicit conversions can get you in trouble, even when it is a non plan affecting conversion. However, using the BETWEEN operator where the end value is “2014-01-01 00:00:00” could potentially include unwanted records. Instead, the end value would need to be “2014-12-31 23:59:59.999”. The number of decimal places necessary would depend on the actual data type. This is why, in my opinion, the BETWEEN operator is not a good choice for datetime data types and also decimal based data types. It is great for int data types. Instead, a >= and < pattern is the better choice.
It is these kind of things that make SQL a challenge where the query will return the expected results must of the time!
LikeLiked by 1 person