How to: Specify Fill Factors for Indexes (Visual Database Tools)
In Microsoft SQL Server databases, you can identify a fill factor to specify how full each index page can be. The fill factor is the percentage of space on each leaf level page to be filled with data, therefore reserving a percentage of 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 amount of empty space on an index page is important because when an index page fills up, the system must take time to split it to make room for new rows.
Specify a fill factor when you want to fine-tune performance. It is useful when you are creating a new index on a table with existing data, and particularly when you can accurately predict future changes in that data.
To specify a fill factor for an index
In Object Explorer, right-click the table with an index for which you want to specify a fill factor and click Design (Modify in SP1 or earlier).
The table opens in Table Designer.
From the Table Designer menu, click Indexes/Keys.
The Indexes/Keys dialog box opens.
Select the index in the Selected Primary/Unique Key or Index list.
In the Fill Factor box, type a number from 0 to 100.
Note
A value of 100 means the pages will be full and will take the least amount of storage space. This setting should be used only when there will be no changes to the data, for example, on a read-only table. A lower value leaves more empty space on the data pages, which reduces the need to split data pages as indexes grow but requires more storage space.
See Also
Concepts
Creating Indexes (Database Engine)
Fill Factor
Other Resources
Indexes/Keys Dialog Box (Visual Database Tools)