Create Clustered Indexes

Article
06/13/2017

You can create clustered indexes on tables in SQL Server 2014 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 topic Clustered and Nonclustered Indexes Described.)

In This Topic

Before you begin:

Typical Implementations

Limitations and Restrictions

Security
To create a clustered index on a table, using:

SQL Server Management Studio

Transact-SQL

Before You Begin

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 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 and Restrictions

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 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 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 Perform Index Operations Online.
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. To obtain information about tables that might contain row-overflow data, use the sys.dm_db_index_physical_stats (Transact-SQL) dynamic management function.

Security

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.

Using SQL Server Management Studio

To create a clustered index by using 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, click Add....
In the Select Columns fromtable_name dialog box, select the check box of the table column to be added to the clustered index.
Click OK.
In the New Index dialog box, click OK.

To 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 click New Table....
Create a new table as you normally would. For more information, see Create Tables (Database Engine).
Right-click the new table created above and click Design.
On the Table Designer menu, click Indexes/Keys.
In the Indexes/Keys dialog box, click 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.
Click Close.
On the File menu, click Savetable_name.

Using Transact-SQL

To create a clustered index

In Object Explorer, connect to an instance of Database Engine.
On the Standard bar, click New Query.

Copy and paste the following example into the query window and click Execute.

USE AdventureWorks2012;  
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).

Share via