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?
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..
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).
