Microsoft KB Archive/233553

= FIX: Non-Clustered Fillfactor Reset When Clustered Index Created =

Article ID: 233553

Article Last Modified on 3/14/2006

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q233553



BUG #: 54186 (SQLBUG_70)



SYMPTOMS
If you create a clustered index on a table that already has a nonclustered index, the fillfactor applied for the nonclustered index is reset by the fillfactor of the clustered index.



WORKAROUND
Recreate the non-clustered index with the correct fillfactor.



STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 1 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

232570INF: How to Obtain Service Pack 1 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0

For more information, contact your primary support provider.



MORE INFORMATION
In SQL Server 6.5, the Sysindexes table retained the Original fillfactor that was used to create each index, even though it was not used for subsequent data modifications and was created for a one time use only, at the index creation time. The fillfactor was saved in Sysindexes table just in case you need to know how the indexes were created.

In SQL Server 7.0 "Golden version", the fillfactor used with the nonclustered index is overwritten in the Sysindexes table by the fillfactor used with the clustered index, if the clustered index is created after the nonclustered index.

In SQL Server 7.0 SP1, the problem is resolved and the Sysindexes table retains all the original fillfactors of all the indexes of a table.

Additional query words: Sysindexes, clustered index, nonclustered fillfactor, overwritten, reset, Original OrigFillFactor

Keywords: kbbug kbfix KB233553

-

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

© Microsoft Corporation. All rights reserved.