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 :)
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.
I would be very surprised if that was sargable. One option might be to rewrite it as:
WHERE p.PlayerStatusLastTransitionDate >= DATEADD(month,1,CAST(@now AS DATE)) AND p.PlayerStatusLastTransitionDate <= DATEADD(month,7,CAST(@now AS DATE)) Which I believe will be sargable (even though it's not quite as pretty).
table.field BETWEEN f(x) AND f(y) (or using >= and <= as Abe has done) is SARGable. Pretty much, if you put the search field in a function, it's not going to be SARGable. Instead, process the parameters to the search, like Abe's answer :)
BETWEEN. The important thing is that the date function is being applied to expressions that are constant for the execution of the query, rather than being applied to each row as in the OP's query.
