Programming in MS SQL Server 2012

Fill Factor Option

The fill-factor option is provided for fine-tuning index data storage and performance. When an index is created or rebuilt, the fill-factor value determines the percentage of space on each leaf-level page to be filled with data, reserving the remainder on each page as free space for future growth.
For example, specifying a fill-factor value of 80 means that 20 percent of each leaf-level page will be left empty, providing space for index expansion as data is added to the underlying table. The empty space is reserved between the index rows rather than at the end of the index.
The fill-factor value is a percentage from 1 to 100, and the server-wide default is 0 which means that the leaf-level pages are filled to capacity. A nonzero fill factor other than 0 or 100 can be good for performance if the new data is evenly distributed throughout the table. However, if all the data is added to the end of the table, the empty space in the index pages will not be filled.
For example, if the index key column is an IDENTITY column, the key for new rows is always increasing and the index rows are logically added to the end of the index. If existing rows will be updated with data that lengthens the size of the rows, use a fill factor of less than 100. The extra bytes on each page will help to minimize page splits caused by extra length in the rows.
The following example describes drops and re-creates existed nonclustered index with fill factor option.

-- Example 154 --

USE EBusiness

Drops and re-creates the IX_Employee_Nonclustered index on the dbo.Employee table with a fill factor of 80

CREATE NONCLUSTERED INDEX IX_Employee_Nonclustered

ON dbo.Employee(Dept_ID)

WITH (DROP_EXISTING = ON, FILLFACTOR = 80)

Query Output Screen

* * * * *

Your Comment To AUTHOR