Microsoft KB Archive/304519

= FIX: Nonclustered Indexes Rebuilt For CREATE UNIQUE CLUSTERED INDEX ... WITH DROP_EXISTING Query =

Article ID: 304519

Article Last Modified on 10/9/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q304519



BUG #: 354670 (SHILOH_BUGS)



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.



STATUS
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.



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.

Keywords: kbbug kbfix kbsqlserv2000presp2fix KB304519

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.