reservepagegap and sorted_data options

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:

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 3-10 shows how these rules apply.

Table 3-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.