Creating Clustered Indexes
With few exceptions, every table should have a clustered index. Besides improving query performance, a clustered index can be rebuilt or reorganized on demand to control table fragmentation. A clustered index can also be created on a view.
Typical Implementations
Clustered indexes are implemented in the following ways:
- PRIMARY KEY and UNIQUE constraints
When you create a PRIMARY KEY constraint, a unique clustered index on the column or columns is automatically created if a clustered index on the table does not already exist and you do not specify a unique nonclustered index. The primary key column cannot allow NULL values.
When you create a UNIQUE constraint, a unique nonclustered index is created to enforce a UNIQUE constraint by default. You can specify a unique clustered index if a clustered index on the table does not already exist.
An index created as part of the constraint is automatically given the same name as the constraint name. For more information, see PRIMARY KEY Constraints and UNIQUE Constraints. - Index independent of a constraint
You can create a clustered index on a column other than primary key column if a nonclustered primary key constraint was specified. - Indexed view
To create an indexed view, a unique clustered index is defined on one or more view columns. The view is materialized and the result set is stored in the leaf level of the index in the same way table data is stored in a clustered index. For more information, see Creating Indexed Views.
Key Column Selection
Because you can only have one clustered index per table, determining on which columns to create a clustered index is very important. For more information about selecting the right column, see Clustered Index Design Guidelines.
The index key of a clustered index cannot contain varchar columns that have existing data in the ROW_OVERFLOW_DATA allocation unit. If a clustered index is created on a varchar column and the existing data is in the IN_ROW_DATA allocation unit, subsequent insert or update actions on the column that would push the data off-row will fail. For more information about allocation units, see Table and Index Organization.
Disk Space Requirements
When a clustered index structure is created, disk space for both the old (source) and new (target) structures is required in their respective files and filegroups. The old structure is not deallocated until the complete transaction commits. Additional temporary disk space for sorting may also be required. For more information, see Determining Index Disk Space Requirements.
Performance Considerations
If a clustered index is created on a heap with several existing nonclustered indexes, all the nonclustered indexes must be rebuilt so that they contain the clustering key value instead of the row identifier (RID). Similarly, if a clustered index is dropped on a table that has several nonclustered indexes, the nonclustered indexes are all rebuilt as part of the DROP operation. This may take significant time on large tables.
The preferred way to build indexes on large tables is to start with the clustered index and then build any nonclustered indexes. Consider setting the ONLINE option to ON when you create indexes on existing tables. When set to ON, long-term table locks are not held. This enables queries or updates to the underlying table to continue. For more information, see Performing Index Operations Online.
To create a PRIMARY KEY or UNIQUE constraint when you create a table
To create a PRIMARY KEY or UNIQUE constraint on an existing table
To create an index
See Also
Concepts
Clustered Index Structures
Creating Indexes (Database Engine)