If you need to redistribute data on partitions, your choice of method depends on how much space the data occupies on the partition. If the space the table occupies is less than 40 to 45% of the space in the segment, you can create a clustered index to redistribute the data.
If the table occupies more than 40 to 45% of the space on the segment, you need to bulk copy the data out, truncate the table, and copy the data in again. The steps you take depend on whether you need a clustered index and whether the data is already in clustered key order.
Use sp_helpsegment and sp_spaceused to see if there is room to create a clustered index on the segment.
If there is enough space, see “Distributing data evenly across partitions” for the steps to follow. If you do not need the clustered index, you can drop it without affecting the data distribution.
Dump the database after creating the clustered index.
If there is enough space for a copy of the table, you can copy the table to another location and then re-create the clustered index to copy the data back to the target segment.
The steps vary, depending on the location of the temporary storage space:
On the default segment of the database or in tempdb
On other segments in the database
Use select into to copy the table to the default segment or to tempdb.
select * into temp_sales from sales
or
select * into tempdb..temp_sales from sales
Drop the original table.
Partition the copy of the table.
Create the clustered index on the segment where you want the table to reside.
Use sp_rename to change the table’s name back to the original name.
Dump the database.
If there is space available on another segment:
Create a clustered index, specifying the segment where the space exists. This moves the table to that location.
Drop the index.
Re-create the clustered index, specifying the segment where you want the data to reside.
Dump the database.
If there is not enough space, and you need a to re-create a clustered index on the tables:
Copy out the data using bulk copy.
Unpartition the table.
Truncate the table with truncate table.
Drop the clustered index using drop index or alter table...drop constraint.
Then, drop nonclustered indexes, to avoid deadlocking during the parallel bulk copy sessions.
Repartition the table.
Copy the data into the table using parallel bulk copy. You must take care to copy the data to each segment in index key order, and specify the number of rows for each partition to get good distribution.
Re-create the index using the with sorted_data and on segment_name clauses. This command performs a serial scan of the table and builds the index tree, but does not copy the data.
Do not specify any of the clauses that require data copying (fillfactor, ignore_dup_row, and max_rows_per_page).
Re-create any nonclustered indexes.
Dump the database.
If there is no clustered index, and you do not need to create one:
Copy the data out using bulk copy.
Unpartition the table.
Truncate the table with truncate table.
Drop nonclustered indexes, to avoid deadlocking during the parallel bulk copy in sessions.
Repartition the table.
Copy the data in using parallel bulk copy.
Re-create any nonclustered indexes.
Dump the database.
To change index keys on the clustered index of a partitioned table, or if you want to create an index on a table that has been stored as a heap, performing an operating system level sort can speed the process.
Creating a clustered index requires 120% of the space used by the table to create a copy of the data and build the index tree.
If you have access to a sort utility at the operating system level:
Copy the data out using bulk copy.
Unpartition the table.
Truncate the table with truncate table.
Drop nonclustered indexes, to avoid deadlocking during the parallel bulk copy in sessions.
Repartition the table.
Perform an operating system sort on the file.
Copy the data in using parallel bulk copy.
Re-create the index using the sorted_data and on segment_name clauses. This command performs a serial scan of the table and builds the index tree, but does not copy the data.
Do not specify any of the clauses that require data copying (fillfactor, ignore_dup_row, and max_rows_per_page).
Re-create any nonclustered indexes.
Dump the database.
