Commands to create and drop clustered indexes

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:

For more information on configuring Adaptive Server to allow parallel execution, see “Controlling the degree of parallelism”.

See Chapter 26, “Parallel Sorting,” 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.