DetailPage-MSS-KB

Microsoft small business knowledge base

Article ID: 304519 - Last Review: October 9, 2003 - Revision: 3.1

This article was previously published under Q304519
BUG #: 354670 (SHILOH_BUGS)

On This Page

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  (http://support.microsoft.com/kb/290211/EN-US/ ) 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.

APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
Keywords: 
kbbug kbfix kbsqlserv2000presp2fix KB304519
Share
Additional support options
Ask The Microsoft Small Business Support Community
Contact Microsoft Small Business Support
Find Microsoft Small Business Support Certified Partner
Find a Microsoft Store For In-Person Small Business Support