Background on the sorted_data option

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:

Table 14-10 shows how these rules apply.

Table 14-10: reservepagegap and sorted_data options

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.