1

I have a query which works well with either WHERE clause.

WHERE DATEDIFF(day, [EventStartDateTime], GETDATE()) <= 60

or

WHERE EventStartDateTime < DATEADD(DY, -60, GETDATE())

My question is, which is more efficient? How? And is there a way to measure this in the future to test for myself?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Rob
  • 1,045
  • 13
  • 28

2 Answers2

3

use the second one,it is sargable and can use indexes if present

EventStartDateTime < DATEADD(DY, -60, GETDATE())

Also WHERE clause is not the only place where Sargabilty matters.it can also have an effect on ORDER BY, GROUP BY and HAVING clauses.

And is there a way to measure this in the future to test for myself?

You can look at the execution plan ,to see if it is doing a seek or Scan. This doesn't mean seek is good or scan is bad.when you have index and it is Sargable,it will try* to seek the value,rather than scanning the whole table

Please see below link which has more details on same,as pointed by @AdatheDev in comments..

Is this date comparison condition SARG-able in SQL?

Community
  • 1
  • 1
TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
  • +1, avoid function calls around columns. Extra reference for this scenario/sargability: http://stackoverflow.com/questions/10853774/is-this-condition-sargable – AdaTheDev Oct 12 '16 at 14:06
  • yes,I am trying to find Itzik Ben-Gan topic on the same topic – TheGameiswar Oct 12 '16 at 14:07
0
  SELECT <column list> 
  FROM schemaname.tablename -- please always specify schema 
  WHERE EventStartDateTime < DATEADD(DY, -60, GETDATE())

The reason this way is efficient is that it makes query more optimized as the index will be put on EventStartDateTime . if you dont put index today you may want to use it in future.Thus applying functions like DATEDIFF() to the column will always make the expression more complex, so it will always use a less efficient scan (if you use a single search predicate ofcourse).

khakishoiab
  • 9,673
  • 2
  • 16
  • 22