0

I was going through 070-461 MS Book which is based on SQL Server 2012 (& SQL Server 2014 now) and found this section of query where it says should not be using non-clustered index because we are not using SARGs syntax in WHERE predicate.

SELECT orderid, custid, orderdate, shipname
FROM Sales.Orders
WHERE DATEDIFF(day, '20060709', orderdate) <= 2
  AND DATEDIFF(day, '20060709', orderdate) > 0;

Same has been supported by the link here: What makes a SQL statement sargable?

I tested the exact same query on SQL Server 2014 and I do see it using non-clustered index (as if it is SARGable)

I couldn't find anything that says this is changed in SQL Server 2014 or that SARGs would accept the column

Any ideas on what I am missing?

This is the screenshot from the book:

enter image description here

And here the screenshot from my lab using SQL Server 2014

enter image description here

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
SunMan
  • 53
  • 4
  • A non-sargable expression can use indexes, just not efficiently (scan versus seek). The only benefit of the non-clustered index here is that it covers the query. – Dan Guzman Jul 08 '18 at 12:11

1 Answers1

1

Yes, a non-sargable query can use non clustered indexes. Non-sargable expressions prevent seek operations on indexes, but it allows index scans.

The cardinality estimator changed in SQL Server 2014, I guess this is the reason why the query plan changed.

The query is still non-sargable as you see on the query plan, it scans the index entirely. If it was sargable or SQL Server was smart enough to transform the predicate to a sargable expression it would seek the index.

SQL Server can solve the query in two different ways:

Way 1:

  1. Scan the clustered index filtering the results by date. Imagine the clustered has 25 pages. The IO cost is the cost of reading 25 pages.

Way 2:

  1. Scan the non clustered index filtering the results by date. This index is much smaller than the clustered index because it only contains date and orderid (clustered key) columns. Imagine it has 6 pages. The IO cost is the cost of reading 6 pages. The result of this index scan are the corresponding clustered keys orderid's.
  2. Key lookup every orderid found on step 1 to get columns custid and shipname. Imagine it finds 2 orderid's, then it has to perform two key lookups. Key lookups require 2 to 4 page reads each. So, it needs, at most, 8 page reads for key lookups.

So, way 1 requieres 25 logical reads and way 2 requires 14 logical reads. Way 2 is more efficient than way 1.

But way 2 is more efficient than way 1 only if the number of rows matching the predicate is small enough and the index size difference is big enough. For example, with 10 matching rows it requires 6 + 10 * 4 = 46, which is more than the required logical reads for way 1.

SQL Server will choose way 2 if it estimates that number of matching rows is small enough and the non clustered index is smaller enough than the clustered index.

SQL Server changed the cardinality estimator on SQL Server 2014, so it might be the cause why the query plan changed.

Jesús López
  • 8,338
  • 7
  • 40
  • 66