Specify a Fill Factor for an Index

In Microsoft SQL Server databases, you can identify a fill factor to specify how full each index page can be. 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.

It is seldom necessary to specify a fill factor when you create an index. The option is provided for fine-tuning 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.

Specify a fill factor for an index on a table

1. In the Database window, click Database Diagrams under Objects, click the database diagram you want to open, and then click Design on the database window toolbar.
2. In your database diagram, right-click the table containing the index for which you want to specify a fill factor, and choose Properties from the shortcut menu.
3. Choose the Indexes/Keys tab.
4. Select the index from the Column name list.
5. In the Fill factor box, type a percentage from 0 to 100.

Specify a fill factor for an index on a view (Microsoft SQL Server 2000 only)

1. In the Database window, click Queries   under Objects, click the view you want to open, and then click Design on the database window toolbar.
2. Right-click in the Query Designer, and choose Manage Indexes from the shortcut menu.
3. Select the index from the Column name list.
4. In the Fill factor box, type a percentage from 0 to 100.

For more information about fill factors and their uses, see the SQL Server documentation.