By default, Adaptive Server creates indexes that are completely full at the leaf level and leaves growth room for two rows on the intermediate pages.
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 use fillfactor, except for a fillfactor value of 100 percent, 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 for create index.
The fillfactor is used only when you create the index; it is not maintained over time.
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.
fillfactor values specified with create index are applied at the time the index is created. They are not saved in sysindexes, and the fullness of the data or index pages is not maintained over time.
You can also use sp_chgattribute to store values for fillfactor that are used when reorg rebuild is run on a table.
See “Setting fillfactor values” for more information.
