You can create a clustered index using the create clustered index command or by creating a primary or foreign key constraint with alter table...add constraint. The steps to drop and re-create it are slightly different, depending on which method you used to create the existing clustered index.
Creating a clustered index on a partitioned table requires a parallel sort. Set configuration parameters and set options as shown before you issue the command to create the index:
Set number of worker processes and max parallel degree to at least the number of partitions in the table, plus 1.
Execute sp_dboption "select into/bulkcopy/pllsort", true, and run checkpoint in the database.
For more information on configuring Adaptive Server to allow parallel execution, see “Controlling the degree of parallelism” on page 154 in the Performance and Tuning: Optimizer book.
See Chapter 9, “Parallel Sorting,” in the Performance and Tuning: Optimizer book for additional information on parallel sorting.
If your queries do not use the clustered index, you can drop the index without affecting the distribution of data. Even if you do not plan to retain the clustered index, be sure to create it on a key that has a very high number of data values. For example, a column such as “sex”, which has only the values “M” and “F”, will not provide a good distribution of pages across partitions.
Creating an index using parallel sort is a minimally logged operation and is not recoverable. You should dump the database when the command completes.
