A better way to search dates

SARGability is the ability of the query to properly search the arguments that you pass it. When your query is not sargable you cannot properly use your available indexes. A few months ago I wrote about sargability in detail.

Today I want to show a example of how rethinking a solution, with sargability in mind, can provide you with extreme performance benefits.

Date SARGability

I have been presented this simple business requirement.

Find all people who are 65 years or older.

My first though was to query all people and check to see if their age is 65 or greater. In T-SQL it is:

SELECT COUNT(*)
FROM dbo.Person p
WHERE DATEDIFF(YEAR,p.DateOfBirth,GETDATE()) >= 65

With the above query I earned myself an index scan and 15800 logical reads.

date-predicate-1

search-stats-1

I then changed my thought process to find the age of a 65 year old who’s birth day is today. Then I compare the DateOfBirth column to that static value.

SELECT COUNT(*)
FROM dbo.Person p
WHERE DateOfBirth <= DATEADD(YEAR,-65,GETDATE())

With the above query I bought myself an index seek and 345 logical reads. That works out to <3% of the cost.

date-predicate-2

search-stats-2

Wrap-up

Performing functions on the column being filtered prevents index seeks because each record must be read and sent into the function before it can compare the values on each side of the predicate. Often, how we think of a problem does not translate well to how SQL Server queries data. Be cognisant of sargability when writing your WHERE and JOIN predicates.

NOTE: The two queries also do not return the same results. I chose not to update the queries because I feel the one I am recommending returns the more accurate result-set and changing the first query to be more accurate would distract from the concept that I am trying to explain.

This article has 1 comment

  1. […] Derik Hammer shows the right way and the wrong way of using date functions in a WHERE clause: […]

Leave a Reply