Create a clustered index

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance SQL database in Microsoft Fabric

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 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 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 the IN_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

1. In Object Explorer, expand the table on which you want to create a clustered index.

2. Right-click the Indexes folder, point to New Index, and select Clustered Index....

3. In the New Index dialog box, on the General page, enter the name of the new index in the Index name box.

4. Under Index key columns, select Add....

5. In the Select Columns from table_name dialog box, select the check box of the table column to be added to the clustered index.

6. Select OK.

7. In the New Index dialog box, select OK.

Create a clustered index by using the Table Designer

1. In Object Explorer, expand the database on which you want to create a table with a clustered index.

2. Right-click the Tables folder and select New Table....

3. Create a new table as you normally would. For more information, see Create tables (Database Engine).

4. Right-click the new table created previously, and select Design.

5. On the Table Designer menu, select Indexes/Keys.

6. In the Indexes/Keys dialog box, select Add.

7. Select the new index in the Selected Primary/Unique Key or Index text box.

8. In the grid, select Create as Clustered, and choose Yes from the drop-down list to the right of the property.

9. Select Close.

10. On the File menu, select Save table_name.

Use Transact-SQL

1. In Object Explorer, connect to an instance of Database Engine.

2. On the Standard bar, select New Query.

3. 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).

Related content

• Create Primary Keys
• Create unique constraints