Create Clustered Index For Other Than Primary Key Column in SQL Server

Introduction

A Clustered Index is automatically created on the table when we create a PRIMARY KEY constraint, a unique Clustered Index on the column or columns on if there is no Clustered Index specified on the table. SQL Server creates a non-Clustered Index by creating a UNIQUE constraint if the Clustered Index is specified on the table. When the index is created as the part of a constraint, the name of the index is the same as the constraint name. The same as when the constraint us dropped, the indexes present on the constraint are automatically deleted.

Problem statement

Can we create a Clustered Index other than the primary key column in SQL Server?

Suppose I have a table, PKTest and it has Id, Code and Name columns. In this table “Id” is Primary key, so Cluster index is automatically created on this table. My table definition is as in the following. This table also contains data.

CREATE TABLE [dbo].[PKTest]([Id] [int] NOT NULL,[Code] [varchar](50) NULL,[Name] [varchar](50) NULL,CONSTRAINT [PK_PKTest] PRIMARY KEY CLUSTERED ([Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]

Using the following query, we are able to understand Clustered Index name and column name on which the Clustered Index is created.

SELECT t.name as TableName, i.name as ClusteredIndexName, c.Name as ColumnName
FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
INNER JOIN sys.index_columns ic ON i.index_id = ic.index_id AND i.object_id = ic.object_id
INNER JOIN sys.columns c ON ic.column_id = c.column_id AND ic.object_id = c.object_id
WHERE i.type_desc = 'CLUSTERED' and t.name = 'PKTest'

Now I want to remove the cluster index on the Id column and create the Clustered Index on the “Code” column. Is it possible?

Solution

Yes this is possible, we can create a Clustered Index other than the primary key. Use the following procedure to create (or change) the Clustered Index on an existing table.

Step 1: Drop the primary key constraint

Here the Index is created as a part of the primary key constraint, so it will be deleted automatically with the dropping of the primary key constraint.

--Deleting Primary key constraint
IF (EXISTS(select * from sys.key_constraints where name = 'PK_PKTest'))
ALTER table PKTest DROP PK_PKTest
--Deleting index
-- following command useful when we want to delete index which is not created with any constraint
IF (EXISTS(select * from sys.indexes where name = 'PK_PKTest'))
DROP INDEX PK_PKTest on PKTest

Step 2: Create the clustered index on the column that you want.

CREATE CLUSTERED INDEX IX_PKtest_Code ON dbo.pktest (Code);

Step 3: Re-create the primary key

ALTER TABLE pktest
ADD CONSTRAINT PK_PKTest PRIMARY KEY (Id)

Recheck your Clustered Index.

Point to note when creating a new table and do not want to create a Clustered Index on the primary key

When we create a table using a GUI (SQL Server Management Studio) and defining a primary key, SQL Server automatically creates the clustered index on this column. But we can change it using the "Indexes/Keys" property of that column. When you create a table using a SQL script, write the create table script without the word "CLUSTERED". Now SQL Server will create a Non-Clustered Index on the primary key.

Conclusion

By default a clustered index is created with a primary key. That is not always a good choice, but it depends on how the data has been accessed. This article will help us to understand how to create a clustered index other than the primary key.

I hope this helps!