Convert Non Clustered PKs to Clustered

Question

Convert Non Clustered PKs to Clustered

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101098 More actions · Answer 1

Jeff Moden (1/9/2013)
ScottPletcher (1/9/2013)
Don't automatically assume the PK makes the best, or even a good, clustered index, in particular if the PK is an identity column.
Considering that the IDENTITY column is frequently the only column that meets the best conditions of "narrow, unique, and ever increasing", I'm not sure why you'd say such a thing. At best, "It Depends".

Because I'm more concerned about overall performance than following a default, simplistic "rule".

The best clus key does indeed "depend", so it's malpractice to just assume an identity column automatically makes even a good clustering key. The vast majority of the time it does not make the best clus key.

For a PK, it's fine. And if you need it, a non-clus index on identity values works great to do single identity lookups.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

akshay.pawar123 Old Hand Points: 391 More actions · Answer 2

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.

ScottPletcher SSC Guru Points: 101098 More actions · Answer 3

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".