FIX: Nonclustered Indexes Rebuilt For CREATE UNIQUE CLUSTERED INDEX ... WITH DROP

SYMPTOMS

When you use the following syntax for a unique clustered index

 CREATE UNIQUE CLUSTERED INDEX ... WITH DROP_EXISTING

-or-

DBCC DBREINDEX(table_name, clustered_index_name)

Both the clustered index and the nonclustered indexes are rebuilt.

With SQL Server 7.0 only the clustered index is rebuilt. Because both types of indexes are rebuilt in SQL Server 2000, the operation may take longer in SQL Server 2000 than it did in SQL Server 7.0.

RESOLUTION

To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

Hotfix

NOTE: The following hotfix was created prior to Microsoft SQL Server 2000 Service Pack 2.

The English version of this fix should have the following file attributes or later:

   Version      File name       Platform
   -------------------------------------
   8.00.426     s80426i.exe     x86

NOTE: Due to file dependencies, the most recent hotfix or feature that contains the preceding files may also contain additional files.

WORKAROUND

To work around this problem, consider using the new DBCC INDEXDEFRAG command that is introduced in SQL Server 2000. You can use DBCC INDEXDEFRAG while the system is online; however, DBCC INDEXDEFRAG may not be as effective at making the leaf level of the index contiguous as recreating the clustered index.

MORE INFORMATION

For a table that contains a clustered index, the nonclustered index keys contain the clustered key as the row locator, or bookmark. For a unique clustered index, rebuilding the clustered index does not change the index keys, so the nonclustered index keys do not change. As a result, the nonclustered index entries do not need to be rebuilt with the clustered index.

If the clustered index was not initially created as unique, SQL Server internally appends a unique four byte value to the end of each index key. The unique four byte value is required so that each nonclustered index row contains a unique clustered index key. For a non-unique clustered index, this four byte value at the end of the index key may change during the index rebuild, and thus all of the nonclustered index keys must also be rebuilt. If the user did not specify that the clustered index was unique, the expected behavior during a reindex is for all of the indices to be rebuilt.

FIX: Nonclustered Indexes Rebuilt For CREATE UNIQUE CLUSTERED INDEX ... WITH DROP_EXISTING Query (304519)