Clustered Index in SQL Server

A Clustered Index in SQL Server defines the order in which data physically stored in a table. It means the SQL cluster index will sort the records first and then store them.

Generally, when you create Primary Key, the Clustered index automatically created by that primary key. However, you can explicitly create a clustered index in SQL Server using the CREATE CLUSTER INDEX statement. Let us see how to create and delete a clustered index in SQL Server using Transact Query, and SQL Server Management Studio

How to create Clustered Index in SQL Server

When you create Primary Key Constraint on a column, it will automatically create Clustered Index on that column for you. It only happens if there is no existing Cluster in a table. Remember, You can create only One SQL Server Cluster Index per Table

In this example, we will show you how to create a Clustered Index in SQL using the Transact SQL statement. Here we will create a Primary Key at the time of table creation. Refer Create Table article.

-- Create Clustered Index in SQL Server
USE [SQL Tutorial]
GO
 
CREATE TABLE [CustomerRecord]
(
  [CustomerKey] [int] NOT NULL PRIMARY KEY,
  [FirstName] [varchar](50) NOT NULL ,
  [LastName] [varchar](50) NULL,
  [EmailAddress] [nvarchar](50) NULL,
  [Profession] [nvarchar](100) NULL,
  [Yearly Income] [money] NULL
)
Messages
--------
Command(s) completed successfully.

Let me use the sp_helpindex stored procedure to check the SQL index on CustomerRecord table.

EXECUTE SP_HELPINDEX CustomerRecord

As you can see, the primary key had created a Clustered and Unique index on the Customer Key column.

Clustered Index in SQL Server 2

You can also use SP_HELP stored procedure to check the same.

EXECUTE SP_HELP CustomerRecord

It will show all the information about the table.

Clustered Index in SQL Server 3

Or you can expand the table on which we created the SQL Server Clustered Index, and expand the Indexes folder

Clustered Index in SQL Server 4

Insert rows into Clustered Index Column

Let me insert a few rows to check the SQL cluster Index functionality. As you can see, we are inserting five records into the table, and the Customer Key values are not in any proper order.

-- Create Clustered Index in SQL Server
USE [SQL Tutorial]
GO
 
INSERT INTO [dbo].[CustomerRecord] 
VALUES (4, 'Imran', 'Khan', '[email protected]', 'Skilled Professional', 15900)
      ,(2, 'Doe', 'Lara', '[email protected]', 'Management', 15000)
      ,(5, 'Ramesh', 'Kumar', '[email protected]', 'Professional', 65000)
      ,(1, 'SQL', 'Tutorial', '[email protected]', 'CEO', 654500)
      ,(3, 'Tutorial', 'Gateway', '[email protected]', 'HR', 95000)
Messages
--------
(5 row(s) affected)

Let me Select the records that we inserted before. From the below image, see that the records are sorted by Customer Key in Ascending Order. This is because, Clustered Index will reorder (rearrange) records.

Clustered Index in SQL Server 6

Delete Clustered Index in SQL Server

Please use the DROP INDEX statement to delete or drop Clustered Index

-- DROP Clustered Index in SQL Server

DROP INDEX CustomerRecord.PK__Customer__95011E647C9C5969

Here, our clustered Index created automatically by the primary key, so we can’t simply delete using this statement. But for explicitly created Clustered Index, this will work.

Clustered Index in SQL Server 7

Use SSMS to Delete Clustered Index in SQL Server

Within the object explorer, Expand the Database folder and select the table on which your Clustered Index present. Next, expand the Indexes folder, and right-click on the key name to select the Delete option

Clustered Index in SQL Server 8

Once you choose the Delete option, Delete Object window will open. Click OK to delete the Index.

Clustered Index in SQL Server 9

Create SQL Clustered Index on Existing table

In this instance, we will show you how to add Clustered Index on the existing table. And the syntax is:

-- Create Clustered Indexes in SQL Server
CREATE CLUSTERED INDEX Index_Name
ON Table_Name (Column_Name(s) ASC/DESC)

It will create Clustered Index on Yearly Income Column present in Customerrecord table

-- Create Clustered Indexes in SQL Server
CREATE CLUSTERED INDEX IX_CustomerRecord_YearlyIncome
ON CustomerRecord ([Yearly Income] ASC)

Now you can see that the Yearly Income column sorts the data in ascending order

Clustered Index in SQL Server 10

This time we created the Clustered Index explicitly. So, let me try the DROP INDEX statement to delete this clustered index.

-- Delete Clustered Indexes in SQL Server
DROP INDEX CustomerRecord.IX_CustomerRecord_YearlyIncome

We successfully deleted the SQL Server clustered index using the Drop Index statement.

Messages
--------
Command(s) completed successfully.

Create Composite Clustered Index in SQL Server

The SQL allows you to create clustered index on multiple columns (but not multiple clustered indexes). So, you can point the same clustered index to Multiple columns. To demonstrate the SQL composite clustered index, we added two more records to the Customer record table

Clustered Index in SQL Server 12

In this example, we are adding a clustered index on Professional column, and Yearly Income column

-- Create Composite Clustered Indexes in SQL Server
CREATE CLUSTERED INDEX IX_CustomerRecord_YearlyIncome
ON CustomerRecord ([Profession] ASC, [Yearly Income] DESC)
Messages
--------
Command(s) completed successfully.

Now you can see, data is sorted by Professional in Ascending Order, and then by Yearly Income in descending order.

Clustered Index in SQL Server 14

Create Clustered Index in SQL Server Management Studio

This section covers the creation of clustered index using management studio

Use Object Explorer to Create Clustered Index

In this example, we show you how to create a Clustered Index using the Management Studio. To do so, please go to the Object Explorer and expand the Database folder in which the table had.

Please expand the table on which you want to create a Clustered Index, and Right-click on the Indexes folder will open the context menu. Please select New Index and then select the Clustered Index.. option

Clustered Index in SQL Server 15

Once you choose the Clustered Index.. option, a new window called New Index will be opened. Please change the Index Name.

Clustered Index in SQL Server 16

Click on the Add button, and select the column name on which you want to specify the SQL Server Clustered Index. For now, we are choosing the Yearly Income column.

Clustered Index in SQL Server 17

Please change the sort order

Clustered Index in SQL Server 18

Now you can see that the table is sorted by Yearly Income in descending order

Clustered Index in SQL Server 19

Create Clustered Index using Table Designer

Please expand the table on which you want to create a Clustered Index in SQL Server Object Explorer. Next, Right-click on it and select the Design option to open the table in the Designer window. Next, Right-click on the column and pick the Set Indexes / Keys… option.

Clustered Index in SQL Server 20

Please click on the Add button to add a Clustered Index. Once you select the Add option, SSMS will create an Index for you.

Clustered Index in SQL Server 21

Next, click on the Browse button beside the Columns option to change the column. Next, we selected the Profession as the Column Name, and Descending is the Sort Order.

Clustered Index in SQL Server 22

Under the Table Designer, Please change the Create As Clustered option from default No to Yes

Clustered Index in SQL Server 23

That’s it! We created the clustered index. Now you can see that the data is sorted by Income in descending order.

Clustered Index in SQL Server 24