When creating indexes in SQL Server, if you do not specify an index fill factor, the fill factor will be 0 (effectively the same as 100%). You can specify an index’s fill factor percentage in a number of different ways. Some of these methods include: The Create Index statement, DBCC DBReindex and Rebuild.
ALTER INDEX ALL ON dbo.Data
REBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON)
GO
DBCC DBREINDEX (Data, ”, 0)
A few caveats:
-
In the create statement you cannot specify 0 for fill factor.
-
In the DBCC DBReindex statement, if you specify the fill factor as zero, this means Reindex the index with the existing index, not 100.
-
You cannot specify 0 for the fill factor in the Rebuild statement.