Article ID: 233553 - View products that this article applies to.
This article was previously published under Q233553
BUG #: 54186 (SQLBUG_70)
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.
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:
232570For more information, contact your primary support provider.
(http://support.microsoft.com/kb/232570/ )INF: How to Obtain Service Pack 1 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0
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.
Article ID: 233553 - Last Review: March 14, 2006 - Revision: 2.1