Article ID: 304519 - View products that this article applies to.
This article was previously published under Q304519
BUG #: 354670 (SHILOH_BUGS)
When you use the following syntax for a unique clustered index
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.
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:
(http://support.microsoft.com/kb/290211/EN-US/ )INF: How to Obtain the Latest SQL Server 2000 Service Pack
HotfixNOTE: 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:
NOTE: Due to file dependencies, the most recent hotfix or feature that contains the preceding files may also contain additional files.
Version File name Platform ------------------------------------- 8.00.426 s80426i.exe x86
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.
Microsoft has confirmed this to be a problem in SQL Server 2000. This problem was first corrected in Microsoft SQL Server 2000 Service Pack 2.
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.