If you want to redistribute the data pages of a table, leaving room for later expansion:
For allpages-locked tables, drop and re-create the clustered index without using the sorted_data option. Specify the desired reservepagegap value in the create clustered index command, if the value stored in sysindexes is not the value you want.
For data-only-locked tables, use sp_chgattribute to set the reservepagegap for the table to the desired value and then drop and re-create 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 specify a reservepagegap with the create clustered index command. Alternatively, you can 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.
If you plan 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
