The space management properties fillfactor, max_rows_per_page, and reservepagegap in the alter table statement apply to indexes that are created for primary key or unique constraints. The space management properties affect the data pages of the table if the constraint creates a clustered index on an allpages-locked table.
Use sp_chgattribute to change max_rows_per_page or reservepagegap for a table or an index, to change the exp_row_size value for a table, or to store fillfactor values.
Re-created as a result of an alter table command that changes the locking scheme for a table from allpages locking to data-only locking or vice versa.
Automatically rebuilt as part of a reorg rebuild command.
To see the space management properties currently in effect for a table, use sp_help. To see the space management properties currently in effect for an index, use sp_helpindex.
fillfactor leaves extra space on pages when indexes are created, but the fillfactor is not maintained over time. It applies to all locking schemes.
max_rows_per_page limits the number of rows on a data or index page. Its main use is to improve concurrency in allpages-locked tables.
reservepagegap specifies the ratio of empty pages to full pages to apply for commands that perform extent allocation. It applies to all locking schemes.
You can store space management properties for tables and indexes so that they are applied during alter table and reorg rebuild commands.
This table shows the valid combinations of space management properties and locking schemes. If an alter table command changes the table so that the combination is not compatible, the values stored in the stored in system tables remain there, but are not applied during operations on the table. If the locking scheme for a table changes so that the properties become valid, then they are used.
|
Parameter |
Allpages |
Datapages |
Datarows |
|---|---|---|---|
|
max_rows_per_page |
Yes |
No |
No |
|
reservepagegap |
Yes |
Yes |
Yes |
|
fillfactor |
Yes |
Yes |
Yes |
|
exp_row_size |
No |
Yes |
Yes |
This table shows the default values and the effects of using the default values for the space management properties.
|
Parameter |
Default |
Effect of Using the Default |
|---|---|---|
|
max_rows_per_page |
0 |
Fits as many rows as possible on the page, up to a maximum of 256 |
|
reservepagegap |
0 |
Leaves no gaps |
|
fillfactor |
0 |
Fully packs leaf pages |