FIX: Non-Clustered Fillfactor Reset When Clustered Index Created

• Microsoft SQL Server version 7.0

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 version 7.0. This problem has been corrected in U.S. Service Pack 1 for Microsoft SQL Server version 7.0. For information about downloading and installing the latest SQL Server Service Pack, see http://support.microsoft.com/support/sql/.

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 : kbbug7.00
Version : winnt:7.0
Platform : winnt
Issue type : kbbug