Is this date comparison condition SARG-capable in SQL?

Question

Is this condition acceptable?

AND DATEDIFF(month,p.PlayerStatusLastTransitionDate,@now) BETWEEN 1 AND 7) 

My rule of thumb is that the function on the left makes the condition unattractive .. but in some places I read that the BETWEEN clause is valid. Does anyone know for sure?

For reference:

• What makes an SQL statement valid?
• http://en.wikipedia.org/wiki/Sargable

NOTE. If any guru ends here, please refresh the Sargable Wikipedia page. I updated it a bit, but I'm sure it can be improved more :)

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 pay attention to the recommended / missing index only on the last request, as it is the only one that could benefit from this:

If we add an index to the OrderDate column, 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 a big difference - the latter uses a search:

Also note how ratings go beyond your version of the query. This can be absolutely disastrous in a large dataset.

There are very few cases where a function or other expression applied to a column is reinforced. One case that I know of is CONVERT(DATE, datetime_column) , but this particular optimization is not documented, and I recommend abandoning it anyway. Not only because you implicitly assumed that using functions / expressions against columns is ok (this is not in all other scenarios), but also because it can lead to waste and catastrophic evaluations .

Answer 2

I would be very surprised if this were reinforced. One option would 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)) 

I believe that this will be acceptable (although this is not entirely true).