nonclustered-indexsql server
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?
For a single row insert you get a narrow/per-row plan
INSERT INTO SomeTable(SomeString1, SomeString2)
SELECT TOP 1 type, type
FROM master..spt_values
If you select the Clustered Index Insert Operator and View the properties window you can see the same information as shown in the XML.
If you try for 1,000 rows
INSERT INTO SomeTable(SomeString1, SomeString2)
SELECT TOP 1000 type, type
FROM master..spt_values
You get a different wide/per-index plan with the operations split out separately
See Wide vs. Narrow Plans or Craig Freedman's blog for more information about the two
You are correct Erik. In case a large portion of the leaf level of the clustered index needs to be read, the size of the other columns affects the amount of data that needs to be read, since the leaf level of a clustered index contains the table pages.
Nonclustered indexes contain the clustered index values for the ability to perform a lookup when a column that is not in the nonclustered index needs to be fetched. The optimizer can leverage this in order to fetch the clustered index values from there when it decided it's cheaper to do that.
Best Answer
If the table has more columns than the 3 described, it would be more efficient to scan the
IX_LastName_Ageindex. If the table is just those 3 columns, it may scan either the clustered index, or theIX_LastName_Ageindex, each would essentially be equivalent. In no case could there be a seek given the info in this scenario.