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?

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?

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.

StackExchange Database Administrators Q#300802, answer score: 13