When you create a clustered index on a table that is already stored on the data pages in index key order, the sorted_data option suppresses the step of copying the data pages in key order for unpartitioned tables. The reservepagegap option can be specified in create clustered index commands, to leave empty pages on the extents used by the table, leaving room for later expansion. There are rules that determine which option takes effect. You cannot use sp_chgattribute to change the reservepagegap value and get the benefits of both of these options.
If you specify both with create clustered index:
On unpartitioned, allpages-locked tables, if the reservepagegap value specified with create clustered index matches the values already stored in sysindexes, the sorted_data option takes precedence. Data pages are not copied, so the reservepagegap is not applied. If the reservepagegap value specified in the create clustered index command is different from the values stored in sysindexes, the data pages are copied, and the reservepagegap value specified in the command is applied to the copied pages.
On data-only-locked tables, the reservepagegap value specified with create clustered index applies only to the index pages. Data pages are not copied.
Besides reservepagegap, other options to create clustered index may require a sort, which causes the sorted_data option to be ignored. For more information, see “Creating an index on sorted data”.
In particular, the following comments relate to the use of reservepagegap:
On partitioned tables, any create clustered index command that requires copying data pages performs a parallel sort and then copies the data pages in sorted order, applying the reservepagegap values as the pages are copied to new extents.
Whenever the sorted_data option is not superseded by other create clustered index options, the table is scanned to determine whether the data is stored in key order. The index is built during the scan, without a sort being performed.
Table 3-10 shows how these rules apply.
Partitioned table |
Unpartitioned table |
||
---|---|---|---|
Allpages-locked table |
|||
create index with sorted_data and matching reservepagegap value |
Does not copy data pages; builds the index as pages are scanned. |
Does not copy data pages; builds the index as pages are scanned. |
|
create index with sorted_data and different reservepagegap value |
Performs parallel sort, applying reservepagegap as pages are stored in new locations in sorted order. |
Copies data pages, applying reservepagegap and building the index as pages are copied; no sort is performed. |
|
Data-only-locked table |
|||
create index with sorted_data and any reservepagegap value |
reservepagegap applies to index pages only; does not copy data pages. |
reservepagegap applies to index pages only; does not copy data pages. |