1

Question about Index usage:

If I create a nonclustered index like

IX_LastName_Age

Where it is created by LastName ASC and Age ASC, and there is clustered index on ID field.

Now if I run a query

Select Age from dbo.user where Age > 10

Would the execution plan use "IX_LastName_Age" or will it scan from Clustered Index?

2

If the table has more columns than the 3 described, it would be more efficient to scan the IX_LastName_Age index. If the table is just those 3 columns, it may scan either the clustered index, or the IX_LastName_Age index, each would essentially be equivalent. In no case could there be a seek given the info in this scenario.

  • So basically if the index contains three columns like IX_LastName_FirstName_Age and the query looking for FirstName, Age where Age > 10, will this index be used. I guess what I am trying to figure is that the order of Columns in index like LastName, FirstName, Age matter? If the index was IX_Age_LastName_FirstName, would the results be any different? – Rizwan Jul 7 '16 at 16:22
  • yes, if the index had age first, eg (age, lastname, firstname) then it would need a single seek only (and then a partial scan of the index to read all the age values). So it would be more efficient for this query. How much more efficient would depend on the distribution of data. See this answer: Clarification on Clustered index scan and Clustered Index Seek – ypercubeᵀᴹ Jul 7 '16 at 16:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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