Before SQL Server 2000 Service Pack 2 (SP2) the rebuild of a clustered index automatically forced all nonclustered indexes of that table to be rebuilt as well. This behaviour has changed with SP2. Now whether a nonclustered index has to be rebuilt depends on how the clustered index was inititially created. What does this mean?
You probably know that you can specify a clustered index explicitely as UNIQUE or not. If you don’t specify it to be UNIQUE, SQL Server automatically adds a 4-byte uniqueifier, to enforce uniqueness when a duplicate value is encountered. And this small difference decides whether nonclustered indexes have to be rebuilt or not. During the rebuild of a non-unique clustered index this uniqueifier is generated anew. And because the nonclustered indexes contain the clustered index keys at their leaf level, it follows that all nonclustered indexes have to be rebuilt as well. A UNIQUE clustered index does not contain the artificial uniqueifier — only the clustered index keys. These values do not change during the rebuild, thus no nonclustered index has to be rebuilt.
]]>