Wanting to search for database records within a timeframe is relatively common, particularly if we want to examine error logs.
How we search can make a large difference. The two queries below search for the same set of records in the same table but the first query has the potential for far worse performance
-- Every event in the last day (DATEDIFF) SELECT * FROM Event e WHERE DATEDIFF(DAY, e.Timestamp, GETUTCDATE()) <= 1
-- Every event in the last day (DATEADD) SELECT * FROM Event e WHERE e.Timestamp BETWEEN DATEADD(DAY, -1, GETUTCDATE()) AND GETUTCDATE()
DATEDIFF uses a column of the table as a parameter the database cannot use any index that uses that column which has the potential to massively impact query performance.
DATEADD doesn't use the column as a parameter of a function so the database can take advantage of any existing index.
This can result in a massive difference in the performance of a query.
A query that allows the database engine to use an index is Sargable (Search ARGument ABLE).
A query is most likely to be non-sargable if the
WHERE clause contains a function operating on a column. Although a similar function in the
GROUP BY or
HAVING clauses can also affect a query.
As a general rule, if you can at all, avoid using functions which operate in columns in clauses other than