The fillfactor option for the create index command allows you to specify how full to make index pages and the data pages of clustered indexes. When you specify a fillfactor value of any amount other than 100%, data and index rows use more disk space than the default setting requires.
If you are creating indexes for tables that will grow in size, you can reduce the impact of page splitting on your tables and indexes by using the fillfactor option.
fillfactor is used when you create an index, and again when you use reorg rebuild to rebuild indexes as part of table reorganization operations (for example, when you rebuild clustered indexes or run reorg rebuild on a table). fillfactor values are not saved in sysindexes, and the fullness of the data or index pages is not maintained over time. fillfactor is not maintained over time during subsequent inserts or updates to the table.
If the leaf-level pages of your index are initially only partially full (because of the fillfactor value), but this free space is used because of subsequent insertions, the leaf-level pages are prone to future splits. Use reorg rebuild...index to build the leaf-level pages, creating them with the specified value for fillfactor so that future insertions do not cause these splits. Run reorg rebuild on the entire index level so the value for fillfactor allows additional space at the leaf level for the whole index. If there is a local index, run reorg rebuild index at the partition level so only leaf pages in the local index partition are adjusted, leaving additional space for future inserts at the leaf level.
Adaptive Server 15.0 and later allows you to run reorg
rebuild...index on local index partitions.
When you issue create index, the fillfactor value specified as part of the command is applied as follows:
Clustered index:
On an allpages-locked table, the fillfactor is applied to the data pages.
On a data-only-locked table, the fillfactor is applied to the leaf pages of the index, and the data pages are fully packed (unless sp_chgattribute has been used to store a fillfactor for the table).
Nonclustered index – the fillfactor value is applied to the leaf pages of the index.
You can also use sp_chgattribute to store values for fillfactor that are used when reorg rebuild is run on a table.
