Oscar Foley Oscar Foley - 5 months ago 19
SQL Question

Is this condition Sargable?

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:



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 :)

Answer

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:

enter image description here

But notice the recommended/missing index only on the latter query, since it's the only one that could benefit from it:

enter image description here

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:

enter image description here

enter image description here

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.