To redistribute the data pages of a table, leaving room for later expansion:
For allpages-locked tables, drop and recreate the clustered index without using the sorted_data option. If the value stored in sysindexes is not the value you want, use create clustered index to specify the desired reservepagegap.
For data-only-locked tables, use sp_chgattribute to set the reservepagegap for the table to the desired value, then drop and recreate the clustered index, without using the sorted_data option. The reservepagegap stored for the table applies to the data pages. If reservepagegap is specified in the create clustered index command, it applies only to the index pages.
To create a clustered index without copying data pages:
For allpages-locked tables, use the sorted_data option, but do not use create clustered index to specify a reservepagegap. Alternatively, specify a value that matches the value stored in sysindexes.
For data-only-locked tables, use the sorted_data option. If a reservepagegap value is specified in the create clustered index command, it applies only to the index pages and does not cause data page copying.
To use the sorted_data option following a bulk-copy operation, a select into command, or another command that uses extent allocation, set the reservepagegap value that you want for the data pages before copying the data, or specify it in the select into command. Once the data pages have been allocated and filled, the following command applies reservepagegap to the index pages only, since the data pages do not need to be copied:
create clustered index title_ix on titles(title_id) with sorted_data, reservepagegap = 32