Using the SQL Server FILLFACTOR feature can reduce page splitting overhead for insert and update operations on a table using a clustered index. Using FILLFACTOR reserves a percentage of free space on each data page of the index to accommodate future expansion.
The FILLFACTOR option allows you to specify how full SQL Server should make each index page, and each data page in the case of a clustered index. This is important from a performance standpoint because when an index page fills up (after enough rows are added), the system must take time to split the index page to make room for the new row. When you're going to add a lot of data to the table after the index is created, specify a low FILLFACTOR. That way, the data is spread over more pages, leaving space for the rows you plan to add.
The FILLFACTOR is not maintained after the index is initially built. However, if index pages become too full and page splits become a performance problem, you can drop and rebuild indexes to reestablish the desired FILLFACTOR.
Using an appropriate FILLFACTOR when indexes are defined allows you to reduce index page splitting and, in the case of clustered indexes, data page splitting. Maintaining free space will avoid page splits and improve online transaction processing performance. In off hours, the clustered index can be rebuilt with the FILLFACTOR to reestablish the space.
Sometimes using a unique index on a table so that no duplicate index values can be inserted reduces processing cost. If all the columns in a WHERE clause use the equality (=) relational operator and are contained in the unique index, the optimizer uses the unique index to process the query because it knows that there is a minimal fixed cost involved in the index.