With a multi column non clustered index, and the SELECT on middle column, why does SQL server query this index rather than scanning the table?

Posted on

Question :

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

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

Answer :

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.

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.

Leave a Reply

Your email address will not be published. Required fields are marked *