Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

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.

share|improve this answer
    
is probably correct. If you have a test environmental, try throwing an index hint on your query to force it to use your index and then compare the 2 explain plans (with and without the index hint). –  SQL3D Jan 19 '11 at 22:12

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.

share|improve this answer
    
9.3 million rows –  Aushin Jan 19 '11 at 20:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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