How to: Specify Fill Factors for Indexes
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 free space allotted when a new index page is created. 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.
Note
A new version of Table Designer appears for databases in the SQL Server 2012 format. This topic describes the old version of Table Designer, which you use with databases in earlier formats of SQL Server.
In the new version, you can change a table definition through a graphical interface or directly in a script pane. If you use the graphical interface, the table’s definition is automatically updated in the script pane. To apply the SQL code in the script pane, choose the Update button. For more information about the new version, see How to: Create Database Objects Using Table Designer.
Note
Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Customizing Development Settings in Visual Studio.
To specify a fill factor for an index
In Server Explorer, right-click the table with an index for which you want to specify a fill factor and click Open Table Definition.
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.