Is this date comparison condition SARG-able in SQL?
database-performance, sql, sql-server
Solution
Using AdventureWorks, if we look at these two equivalent queries:
SELECT OrderDate FROM Sales.SalesOrderHeader
WHERE DATEDIFF(month,OrderDate,GETDATE()) BETWEEN 1 AND 7;
SELECT OrderDate FROM Sales.SalesOrderHeader
WHERE OrderDate >= DATEADD(MONTH, -7, GETDATE())
AND OrderDate <= DATEADD(MONTH, -1, GETDATE());
In both cases we see a clustered index scan:
But notice the recommended/missing index only on the latter query, since it's the only one that could benefit from it:
If we add an index to the OrderDate column, then run the queries again:
CREATE INDEX dt ON Sales.SalesOrderHeader(OrderDate);
GO
SELECT OrderDate FROM Sales.SalesOrderHeader
WHERE DATEDIFF(month,OrderDate,GETDATE()) BETWEEN 1 AND 7;
SELECT OrderDate FROM Sales.SalesOrderHeader
WHERE OrderDate >= DATEADD(MONTH, -7, GETDATE())
AND OrderDate <= DATEADD(MONTH, -1, GETDATE());
We see much difference - the latter uses a seek:
Notice too how the estimates are way off for your version of the query. This can be absolutely disastrous on a large data set.
There are very few cases where a function or other expression applied to the column will be sargable. One case I know of is `CONVERT(DATE, datetime_column)` - but that particular optimization is undocumented, and I recommend staying away from it anyway. Not only because you'd be implicitly suggesting that using functions/expressions against columns is okay (it's not in every other scenario), but also because it can lead to wasted reads and disastrous estimates.
Problem
Is this condition sargable? ``` AND DATEDIFF(month,p.PlayerStatusLastTransitionDate,@now) BETWEEN 1 AND 7) ``` My rule of thumb is that a function on the left makes condition non sargable.. but in some places I have read that BETWEEN clause is sargable. So does any one know for sure? For reference: - What makes a SQL statement sargable? - http://en.wikipedia.org/wiki/Sargable NOTE: If any guru ends here, please do update Sargable Wikipedia page. I updated it a little bit but I am sure it can be improved more :)