Clustered and Non-Clustered Index : Clustered Index

Clustered Index

• Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be sorted in only one order.
• The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. When a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in an un-ordered structure called a heap.

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 non-clustered index. The primary key column cannot allow NULL values.

When you create a UNIQUE constraint, a unique non-clustered 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. 

Index independent of a constraint 

You can create a clustered index on a column other than primary key column if a non-clustered primary key constraint was specified.

Permissions : Requires ALTER permission on the table or view. User must be a member of the sysadmin fixed server role or the db_ddladmin anddb_owner fixed database roles.

How 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 from table_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.

2. How 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 Save table_name.

How To : Create a clustered index using Query

• In Object Explorer, connect to an instance of Database Engine.
• On the Standard bar, click New Query.
• Use below example into the query window and click Execute.

USE LearnDB;

GO

-- Create a new table with three columns.

CREATE TABLE dbo.MyTable

    (TestCol1 int NOT NULL,

     TestCol2 nchar(10) NULL,

     TestCol3 nvarchar(50) NULL);

GO

-- Create a clustered index called IX_MyTable_TestCol1

-- on the dbo.MyTable table using the TestCol1 column.

CREATE CLUSTERED INDEX IX_MyTable_TestCol1 

    ON dbo.MyTable (TestCol1); 

GO