Adding a clustered index to a heap that has nonclustered indexes

Question

Adding a clustered index to a heap that has nonclustered indexes

Jared

SSC Guru

Points: 61793
More actions
November 15, 2012 at 9:23 am

#393608

BOL says:
If a clustered index is created on a heap with several existing nonclustered indexes, all the nonclustered indexes must be rebuilt so that they contain the clustering key value instead of the row identifier (RID).
However, I also read an article by Paul Randal that said the nonclustered indexes are automatically rebuilt. Now, the language in BOL does not say that "YOU" have to rebuild them, just that they must be. That could mean that they do get rebuilt behind the scenes. To test this we looked at the index usage stats after the clustered index was created and saw that the last system scan did not change, which does change when we initiate an index rebuild. Any insight? (Paul? :-))
Jared
CE - Microsoft

Viewing 4 posts - 1 through 3 (of 3 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

If a clustered index is created on a heap and one or more nonclustered index exists, all nonclustered indexes will be rebuilt as part of the process of creating the clustered index.

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

Jared SSC Guru Points: 61793 More actions · Answer 2

GilaMonster (11/15/2012)
If a clustered index is created on a heap and one or more nonclustered index exists, all nonclustered indexes will be rebuilt as part of the process of creating the clustered index.

Thanks Gail! Is there any way to "prove" this by querying the DMV's, or is it done silently in terms of index stats? This is more for me learning and digging into the DMVs, not for proof.

Jared
CE - Microsoft

Mickey Stuewe SSC Eights! Points: 879 More actions · Answer 3

The non-clustered indexes are re-built so that the key from the clustered index can be added to the end of them. That way the non-clustered indexes can use the clustered index in a Key Lookup when the non-clustered index isn't a covering index.

Mickey Stuewe
Sr Database Developer
My blog
Follow me on twitter: @SQLMickey
Connect with me on LinkedIn
--------------------------------------------------------------------------
I laugh loudly and I laugh often. Just ask anyone who knows me.