Create a clustered index
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
You can create clustered indexes on tables by using SQL Server Management Studio or Transact-SQL. 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. (Clustered indexes are defined in the article Clustered and nonclustered indexes.)
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 doesn't already exist and you don't specify a unique nonclustered index. The primary key column can't allowNULL
values.When you create a
UNIQUE
constraint, a unique nonclustered index is created to enforce aUNIQUE
constraint by default. You can specify a unique clustered index if a clustered index on the table doesn't 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 and Foreign Key Constraints and Unique constraints and check 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.
Limitations
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 isn't deallocated until the complete transaction commits. Additional temporary disk space for sorting might also be required. For more information, see Disk Space Requirements for Index DDL Operations.
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 process might 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 aren't held. This enables queries or updates to the underlying table to continue. For more information, see Perform Index Operations Online.The index key of a clustered index can't 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 theIN_ROW_DATA
allocation unit, subsequent insert or update actions on the column that would push the data off-row fail. To obtain information about tables that might contain row-overflow data, use the sys.dm_db_index_physical_stats (Transact-SQL) dynamic management function.
Permissions
Requires ALTER
permission on the table or view. User must be a member of the sysadmin fixed server role or the db_ddladmin and db_owner fixed database roles.
Use SQL Server Management Studio
Create a clustered index from Object Explorer
In Object Explorer, expand the table on which you want to create a clustered index.
Right-click the Indexes folder, point to New Index, and select Clustered Index....
In the New Index dialog box, on the General page, enter the name of the new index in the Index name box.
Under Index key columns, select Add....
In the Select Columns from table_name dialog box, select the check box of the table column to be added to the clustered index.
Select OK.
In the New Index dialog box, select OK.
Create a clustered index by using the Table Designer
In Object Explorer, expand the database on which you want to create a table with a clustered index.
Right-click the Tables folder and select New Table....
Create a new table as you normally would. For more information, see Create tables (Database Engine).
Right-click the new table created previously, and select Design.
On the Table Designer menu, select Indexes/Keys.
In the Indexes/Keys dialog box, select Add.
Select the new index in the Selected Primary/Unique Key or Index text box.
In the grid, select Create as Clustered, and choose Yes from the drop-down list to the right of the property.
Select Close.
On the File menu, select Save table_name.
Use Transact-SQL
In Object Explorer, connect to an instance of Database Engine.
On the Standard bar, select New Query.
Copy and paste the following example into the query window and select Execute.
USE AdventureWorks2022; GO -- Create a new table with three columns. CREATE TABLE dbo.TestTable ( TestCol1 INT NOT NULL, TestCol2 NCHAR(10) NULL, TestCol3 NVARCHAR(50) NULL ); GO -- Create a clustered index called IX_TestTable_TestCol1 -- on the dbo.TestTable table using the TestCol1 column. CREATE CLUSTERED INDEX IX_TestTable_TestCol1 ON dbo.TestTable (TestCol1); GO
For more information, see CREATE INDEX (Transact-SQL).