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 9-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. |
|
