akshay.pawar123 (2/11/2013)
GilaMonster (1/9/2013)
ScottPletcher (1/9/2013)
If SQL needs only key column(s), why does it read all the leaf pages of the table as well?? That's an incredible waste of I/O.The only place that all the rows are stored is at the leaf level of an index. All that's at the non-leaf levels (any index, clustered or nonclustered) is the key columns for the first row on each page of the level below, hence there's no way to get at the key columns without reading the leaf level.
Does it mean that the below two queries are roughly the same from performance point of view?
A. A SELECT query on the key columns of a clustered index.
B. A SELECT query on the key columns of a clustered index plus a couple more nonkey columns.
I might be totally wrong here, but couldnt resist asking this question.
Thanks,
Akshay.
Yes, exactly: that is indeed the point. Unlike with an identity clus, where you have to design and build covering indexes for (almost) every (major) query, then when you add one column the covering index isn't, a cluster is by definition always covering, so once you supply a keyed lookup/range on the clus index, you can add column references w/o ever forcing a table scan.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.