5

A single non clustered is set up on LastName, FirstName, MiddleName - in that order.

SELECT * FROM PERSON
WHERE FirstName='xyz'

Why does the execution plan use the index and not scan the table directly? I am asking because the firstname is the 2nd member of the index and thus is not sorted, so why did SQL server decide the query the non clustered index?

enter image description here

2
  • 4
    Could you add the definition of the table and all the indexes involved? And you can see in the background that your table is missing an index. You might benefit from faster performance, by adding that index. Instead of a screen shot of the query execution plan consider adding the plan to Brent Ozar's | Paste The Plan. You can anonymize the names using Solarwind's | SQL Sentry Plan Explorer if you consider the data confidential. But better responses without.
    – John K. N.
    Oct 7 at 9:45
  • 1
    ....but @MBuschi's answer is quite on the mark.
    – John K. N.
    Oct 7 at 9:56
13

It's a matter of giving results using the minimum IO operations.

The index is much smaller than the whole table and based on statistics SQL Server knows the average cardinality of a single name ('xyz').

So, counting number of pages to read that index plus the number of pages of the lookups (number of occurrance * index depth) to retrive others fields of the table is less than to scan the whole table that is not ordered by name.

That could be the case you are facing.

Try to verify my guess using set statistics io on with and without that index.

3

In addition to MBuschi's answer, if it isn't also clear, but a Scan operation literally iterates through every item in the data structure (e.g. if it's an Index Scan then every item in the underlying B-Tree is scanned), so ordering in the index (for the most part) doesn't matter anymore. Scanning the entire B-Tree of the index is essentially the same (or potentially better for IO reasons, and other reasons such as if it was a filtered index, etc) as scanning the table, as far as for locating the rows needed to serve your query.

Ordering in the index (based on the order of the columns specified in it's definition) matters when the operation is a Seek operation because then it uses the ordering of the nodes in the B-Tree to be able to directly seek out only the rows that match the covered predicate of your query, even more efficiently.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.