FIX: Non-Clustered Fillfactor Reset When Clustered Index Created

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.

Recreate the non-clustered index with the correct fillfactor.

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: For more information, contact your primary support provider.

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.