{"id":154,"date":"2016-06-28T00:00:00","date_gmt":"2013-01-01T00:00:00","guid":{"rendered":"https:\/\/www.sqlhammer.com\/better-way-search-dates"},"modified":"2026-03-18T21:52:34","modified_gmt":"2026-03-18T21:52:34","slug":"better-way-search-dates","status":"publish","type":"post","link":"https:\/\/sqlhammer.com\/index.php\/2016\/06\/28\/better-way-search-dates\/","title":{"rendered":"A better way to search dates"},"content":{"rendered":"<p>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.\u00a0A few months ago I wrote about <a href=\"http:\/\/www.sqlhammer.com\/implicit-conversion-mine-field-blowing-sargability-away\/\">sargability<\/a>\u00a0in detail.<\/p>\n<p>Today I want to show a example of how rethinking a solution, with sargability in mind, can provide you with extreme performance benefits.<\/p>\n<h2>Date SARGability<\/h2>\n<p>I have been presented this simple business requirement.<\/p>\n<p>> Find all people who are 65 years or older.<\/p>\n<p>My first though was to query all people and check to see if their age is 65 or greater. In T-SQL it is:<\/p>\n<pre><code class=\"language-\">SELECT COUNT(<em>)\nFROM dbo.Person p\nWHERE DATEDIFF(YEAR,p.DateOfBirth,GETDATE()) &gt;= 65<\/code><\/pre>\n<p>With the above query I earned myself an index scan and 15800 logical reads.<\/p>\n<p><a href=\"http:\/\/www.sqlhammer.com\/?attachment_id=3108\"><img decoding=\"async\" src=\"\/wp-content\/uploads\/date-predicate-1.jpg\" alt=\"\" \/><\/a><\/p>\n<p><a href=\"http:\/\/www.sqlhammer.com\/?attachment_id=3109\"><img decoding=\"async\" src=\"\/wp-content\/uploads\/search-stats-1.jpg\" alt=\"\" \/><\/a><\/p>\n<p>I then changed my thought process to find the age of a 65 year old who\u2019s birth day is today. Then I compare the DateOfBirth column to that static value.<\/p>\n<pre><code class=\"language-\">SELECT COUNT(<\/em>)\nFROM dbo.Person p\nWHERE DateOfBirth &lt;= DATEADD(YEAR,-65,GETDATE())<\/code><\/pre>\n<p>With the above query I bought myself an index seek and 345 logical reads. That works out to <3% of the cost.<\/p>\n<p><a href=\"http:\/\/www.sqlhammer.com\/?attachment_id=3110\"><img decoding=\"async\" src=\"\/wp-content\/uploads\/date-predicate-2.jpg\" alt=\"\" \/><\/a><\/p>\n<p><a href=\"http:\/\/www.sqlhammer.com\/?attachment_id=3111\"><img decoding=\"async\" src=\"\/wp-content\/uploads\/search-stats-2.jpg\" alt=\"\" \/><\/a><\/p>\n<h2>Wrap-up<\/h2>\n<p>Performing functions on the column being filtered prevents index seeks because each record must be read and sent\u00a0into 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.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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.\u00a0A few months ago I wrote about sargability\u00a0in detail. Today I want to show a example of how rethinking a solution, with sargability in mind, can provide [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":551,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,7,9,10,13,14,21],"tags":[],"class_list":["post-154","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administration","category-community","category-development","category-disaster-recovery","category-microsoft-sql-server","category-performance","category-t-sql"],"_links":{"self":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/154","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/comments?post=154"}],"version-history":[{"count":1,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/154\/revisions"}],"predecessor-version":[{"id":416,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/154\/revisions\/416"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media\/551"}],"wp:attachment":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media?parent=154"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/categories?post=154"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/tags?post=154"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}