Is This Date Comparison Condition Sarg-Able in SQL

Is this date comparison condition SARG-able in SQL?

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.

What makes a SQL statement sargable?

The most common thing that will make a query non-sargable is to include a field inside a function in the where clause:

SELECT ... FROM ...
WHERE Year(myDate) = 2008

The SQL optimizer can't use an index on myDate, even if one exists. It will literally have to evaluate this function for every row of the table. Much better to use:

WHERE myDate >= '01-01-2008' AND myDate < '01-01-2009'

Some other examples:

Bad: Select ... WHERE isNull(FullName,'Ed Jones') = 'Ed Jones'
Fixed: Select ... WHERE ((FullName = 'Ed Jones') OR (FullName IS NULL))

Bad: Select ... WHERE SUBSTRING(DealerName,4) = 'Ford'
Fixed: Select ... WHERE DealerName Like 'Ford%'

Bad: Select ... WHERE DateDiff(mm,OrderDate,GetDate()) >= 30
Fixed: Select ... WHERE OrderDate < DateAdd(mm,-30,GetDate()) 

Why is OR operation or OR condition non-sargable?

The answer was provided by the author of the authoritative optimization book SQL Server Query Performance Tuning, Grant Fritchey. So here it goes:

"OR statements are much more optimized now than they used to be. But if you think about it, if I have an index and I want to match values that are equal to A or Z, the engine has to do multiple comparisons, not simply one. The sargeable conditions all result in a straight forward point lookup, or range lookup. So = A will walk a tree and retrieve the one row, or the set of rows, from the index for that value. But if it’s A or R, it can’t retrieve a range, it has to do other types of work. Sometimes you’ll see these done as two seeks with a JOIN operation. And that’s great. But other times you’ll see additional filter operators or scans. So, it’s not sargeable." (again, credit goes to the author)

SQL date comparison issue

You missed the Time. You are technically searching for 2016-07-14 00:00:00 to 2016-07-15 00:00:00, which only consists 2016-07-14 data.

If you need to find on both 2 days, then:

SELECT 
  * 
FROM 
  table
WHERE
    DateTimeOfInsert >= '2016-07-14 00:00:00' 
  AND 
    DateTimeOfInsert <= '2016-07-15 23:59:59'

or simply use this for the second condition

DateTimeOfInsert <= '20160716'

which implies between 2016-07-14 00:00:00 to 2016-07-16 00:00:00

Is SARGable or not? If not, what can I use instead?

The operator <> is SARGable, but depending on what you are comparing it might not help much that it is.

Ref: http://en.wikipedia.org/wiki/Sargable

"Sargable operators that rarely improve performance: <>,IN,OR,NOT IN,
NOT EXISTS, NOT LIKE"

An operator like = is more likely to give good performance becauce the database can look up a single or a limited number of records from an index. When you use the <> operator, the database often has to scan the entire index to get the relevant records.

A query where the database could make some use of an index with the <> operator would be if it's a non-unique index, and there are few different values in the column so that many records can be elliminated using the index, not just a single or a few records.

---

Related Topics