Is this date comparison condition SARG-able in SQL?

Question

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

Rothwell

BETWEEN is just shorthand for >= AND <=. Why would that do anything to sargability in this case? – Myrlmyrle

Just thinking that the function in the left (DATEDIFF) might affect sargability (wow! what a word!)... – Rothwell

Also please see sqlperformance.com/2013/09/t-sql-queries/datediff-bug – Myrlmyrle

Answer 1

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.

Myrlmyrle

Answer 2

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

Anibalanica

+1 : The OPs query is not SARGable. 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 :) – Frederic

This answer could be rephrased using 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. – Lundeen

I recommend against using BETWEEN for datetime queries, in your own code or for code you're providing to others. Here's why: sqlblog.org/blogs/aaron_bertrand/archive/2011/10/19/… – Myrlmyrle

The link above is stale. This is the new location: sqlblog.org/2011/10/19/… – Instrumentalist