Does rebuilding a clustered index rebuild nonclustered indexes?

  • Article

This came up yesterday with one of our support engineers, so I thought I’d post a brief entry on it.  On SQL Server 2005, using ALTER INDEX…REBUILD to rebuild a clustered index does not rebuild its nonclustered indexes by default.  If you think about it, it makes perfect sense—the clustered keys, which are the record locators in the NC indexes, aren’t changed merely because you rebuild the index.  All rebuilding the index does is lay the pages back down.  The rows may move around on the clustered index pages, but we don’t care because the NC indexes don’t use a RID to reference the data; they use the clustered key and have since SQL Server 7.0.   Naturally, if you completely drop the clustered index, we have to rebuild the NC indexes because their record locators have to be changed to RIDs.  And if you then recreate the clustered index, we have to reverse the process.  So, being able to rebuild the clustered index in place is a real performance boon.  

Not only can ALTER INDEX rebuild a clustered index in place, this is the default behavior.  You have to specify ALL for the index name in order to rebuild all indexes.

Comments

  • Anonymous
    February 27, 2007
    Another nifty note about indexing. One of the more popular interview questions for technical folks is

  • Anonymous
    February 27, 2007
    actually this is a great step forward for index rebuilding, there are times when this is exactly what is required. Thanks, I hadn't explored this option.

  • Anonymous
    June 25, 2007
    Does rebuilding a clustered index rebuild nonclustered indexes? I have discussed this question with one

  • Anonymous
    September 14, 2007
    I have few Questions Ken...

  1. I have checked in SQL Server 2005 That If we rebuild the Clustered Index , it does not rebuild the Non-clusered indexes. Database :Adventureworks Table : Production.Product
  2. secondly , even if i rebuild the indexes , there is no change in the fragmentation ..
  3. in the same database i have seen that if rebuild the indexes ....fragmentation increases .. Pretty confused .........