SQL Server Interview Question #230

Can SQL Server use a composite index if the first key column is not filtered?

Advanced Indexing Senior Advanced

Detailed Explanation

It can sometimes use the index, but it may not be able to perform an efficient seek based solely on a later key column.

For an index on (CustomerId, OrderDate), a query filtering only OrderDate may scan the index or use another access method because the index is primarily ordered first by CustomerId.

SQL Server may still choose the index if it is narrower or otherwise cheaper than alternatives, but this does not mean the later key has the same seek capability as the leading key.