Grant Fritchey (4/28/2009)
SELECT * FROM TEST101
WHERE CODE=105 AND ACNO=14000
ORDER BY CODE
--WHY IT IS GOING FOR CLUSTRED INDEX OF COLUMN "ID"
--Now it is going for non clustered index, this is normal
This is an example of a covering index, so it never even goes to the cluster to return since all the columns are included in the nonclustered index (including the cluster key column, ID).
Actually not. There's no index (other than the cluster) that's covering. I would guess that this query returns relatively few rows and the optimiser's picking one of the nonclustered indexes (probably the one on code, since it will satisfy the order by as well as the seek), seeking on that and then doing a bookmark/key lookup to fetch the other column.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability