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.