Use sp_chgattribute to store a fillfactor percentage for each index and for the table. The fillfactor you set with sp_chgattribute is applied when you:
Run reorg rebuild against tables using any locking scheme.
Use alter table...partition by to repartition a table.
Use alter table...lock to change the locking scheme for a table. or use an alter table...add/modify command that requires copying the table.
Run create clustered index and a value is stored for the table.
See the Reference Manual: Commands for details information about each of these commands.
With the default fillfactor of 0, the index management process leaves room for two additional rows on each index page when you create a new index. When you set fillfactor to 100 percent, it no longer leaves room for these rows.The only effect that fillfactor has on size calculations is when calculating the number of clustered index pages and when calculating the number of non-leaf pages. Both of these calculations subtract 2 from the number of rows per page. Eliminate the -2 from these calculations.
Other values for fillfactor reduce the number of rows per page on data pages and leaf index pages. To compute the correct values when using fillfactor, multiply the size of the available data page (2016) by the fillfactor. For example, if your fillfactor is 75 percent, your data page would hold 1471 bytes. Use this value in place of 2016 when you calculate the number of rows per page. For these calculations, see “Compute the number of data pages” and “Calculate the number of leaf pages in the index”.
Adaptive Server does not apply the stored fillfactor when it builds nonclustered indexes as a result of a create clustered index command:
If a fillfactor value is specified with create clustered index, that value is applied to each nonclustered index.
If no fillfactor value is specified with create clustered index, the server-wide default value (set with the default fillfactor percent configuration parameter) is applied to all indexes.
