Values for fillfactor, exp_row_size, reservepagegap and max_rows_per_page can affect object size.
The fillfactor you specify for create index is applied when the index is created. The fillfactor is not maintained during inserts to the table. If a fillfactor has been stored for an index using sp_chgattribute, this value is used when indexes are re-created with alter table commands and reorg rebuild. The main function of fillfactor is to allow space on the index pages, to reduce page splits. Very small fillfactor values can cause the storage space required for a table or an index to be significantly greater.
See “Reducing index maintenance” for details about setting fillfactor values.
Setting an expected row size for a table can increase the amount of storage required. If your tables have many rows that are shorter than the expected row size, setting this value and running reorg rebuild or changing the locking scheme increases the storage space required for the table. However, the space usage for tables that formerly used max_rows_per_page should remain approximately the same.
See “Reducing row forwarding” for details about setting exp_row_size values.
Setting a reservepagegap for a table or an index leaves empty pages on extents that are allocated to the object when commands that perform extent allocation are executed. Setting reservepagegap to a low value increases the number of empty pages and spreads the data across more extents, so the additional space required is greatest immediately after a command such as create index or reorg rebuild. Row forwarding and inserts into the table fill in the reserved pages.
See “Leaving space for forwarded rows and inserts”.
The max_rows_per_page value (specified by create index, create table, alter table, or sp_chgattribute) limits the number of rows on a data page.
To compute the correct values when using max_rows_per_page, use the max_rows_per_page value or the computed number of data rows per page, whichever is smaller, in “Compute the number of data pages” and “Calculate the number of leaf pages in the index”.
