SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Convert Non Clustered PKs to Clustered


Convert Non Clustered PKs to Clustered

Author
Message
ScottPletcher
ScottPletcher
SSCrazy Eights
SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)

Group: General Forum Members
Points: 8006 Visits: 7162
Btw, it's always frustrated me that SQL Server can't seem to read only the non-leaf pages of a clustered index. 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.

UDB/DB2 can do it, why not SQL Server?

SQL DBA,SQL Server MVP(07, 08, 09)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
robert.nesta123
robert.nesta123
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 19
Additionally, the PK index is now larger (potentially much larger) because it contains all columns. So if you have queries that only needed the PK key columns, then it now has to read a lot more data. This particular scenario can be fixed by adding a replacement nonclustered index on those columns ... though I would evaluate indexing as a whole, because there may be 1 or 2 additional columns you could add that would make it suitable for even more queries.[/quote]

let me just get this straight theni think ive got what i need to do.

Column A PK

If my query needs this column then create an additional non clustered column A index . Then add more covering indexes on the non-clustered index to satisfy more queries when possible.

This will prevent sql using the large clustered pk, it will then use non clustered pk and satisfy multiple queries if possible.

This solution will get rid of the heap and prevent using the larger pk for queries.

Data willnow be ordered on disk get rid of fragmentation as thi will now be sorted by reindexing jobs
Robert Davis
Robert Davis
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2808 Visits: 1623
robert.nesta123 (1/9/2013)


let me just get this straight theni think ive got what i need to do.

Column A PK

If my query needs this column then create an additional non clustered column A index . Then add more covering indexes on the non-clustered index to satisfy more queries when possible.

This will prevent sql using the large clustered pk, it will then use non clustered pk and satisfy multiple queries if possible.


It depends on the queries. Don't automatically make that change. You have to evaluated whether it is the right decision or not.



My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master, SQL Server MVP
Database Engineer at BlueMountain Capital Management
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87564 Visits: 41117
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".

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88581 Visits: 45284
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.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


ScottPletcher
ScottPletcher
SSCrazy Eights
SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)

Group: General Forum Members
Points: 8006 Visits: 7162
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)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
akshay.pawar123
akshay.pawar123
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 245
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
ScottPletcher
SSCrazy Eights
SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)

Group: General Forum Members
Points: 8006 Visits: 7162
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)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search