Leftmost column of Multi-column index not functioning as single column index

Question

I was under the impression that an index like

CREATE NONCLUSTERED INDEX IX_Name_Age ON People
(
   Name ASC,
   Age ASC
)

would be used not only for a query like Select * From People WHERE Name = 'X' and Age = Y but would also function as a single-column index for something like Select * From People WHERE Name = 'Z'

However, in my few tests I've done so far, that doesn't seem to be the case. For example, I have an index on Manufacturer/PartNumber with Manufacturer as the leading column, but a Select * WHERE Manufacturer = 'A' still does a table scan.

Am I doing/interpreting something incorrectly or was my initial assumption wrong?

Answer 1

If the SQL Server thinks that it will be less expensive to use the non-clustered index if you are searching the left most column then it will. The problem in your case (I'm guessing) is that you are doing a SELECT * instead of specifying only the columns that you want back. If you specify columns which are not within the non-clustered index then the SQL Server will need to do a key lookup after the index seek and go back to the clustered index (or the heap if there is no clustered index) to get the rest of the columns.

If you only need a few columns back then specify those, and add them as included columns within the existing index and the SQL Server will start using the index.

Answer 2

How many rows are in the table? SQL Server sometimes won't bother using an index if a table is small enough, since the overhead of RID/bookmark lookups from the nonclustered index might outweigh the performance gains of avoiding a table scan. As long as you keep statistics updated on the table, then SQL Server should resort to the index when it deems it necessary.