Clustered Index in SQL Server

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

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

Create Clustered Index in SQL Server

When you create Primary Key Constraint on the column, it will automatically create SQL Server 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 Clustered Index per Table.

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

 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 it on CustomerRecord table.

EXECUTE SP_HELPINDEX CustomerRecord

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

EXECUTE SP_HELPINDEX 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.

SQL Server Clustered Indexes 3

Or you can expand the table on which we created it and expand the Indexes folder.

Viewing them in Object Explorer 4

Insert rows into SQL Server Clustered Index Column

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

INSERT INTO [dbo].[CustomerRecord] 
VALUES (4, 'Imran', 'Khan', 'abc@abc.com', 'Skilled Professional', 15900)
      ,(2, 'Doe', 'Lara', 'xyz@abc.com', 'Management', 15000)
      ,(5, 'Ramesh', 'Kumar', 'mnop@abc.com', 'Professional', 65000)
      ,(1, 'SQL', 'Tutorial', 'sqltut@abc.com', 'CEO', 654500)
      ,(3, 'Tutorial', 'Gateway', 'tgate@abc.com', '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 because it will reorder (rearrange) records.

SQL Server Clustered Indexes table 6

Delete or Drop Clustered Index in SQL Server

Please use the DROP INDEX statement to delete or drop.

DROP INDEX CustomerRecord.PK__Customer__95011E647C9C5969

Here, the primary key creates our clustered Index automatically, so we can’t simply delete it using this statement. But for explicitly creating only one, this will work.

Delete or Drop SQL Server Clustered Indexes error 7

Delete Clustered Index in SQL Server Management Studio

Within the object explorer, Expand the Database folder and select the table on which it is presented. Next, expand the Indexes folder, and right-click on the key name to select the Delete option.

Delete using SSMS 8

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

Click Ok to Delete 9

Create SQL Server Clustered Index on the Existing table

In this instance, we will show you how to add a clustered index to the existing table. And the syntax is:

CREATE CLUSTERED INDEX Index_Name
ON Table_Name (Column_Name(s) ASC/DESC)

It will create on Yearly Income Column present in Customerrecord table

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

View records on Existing table 10

This time we created it explicitly. So, let me try the DROP INDEX statement to delete the key.

DROP INDEX CustomerRecord.IX_CustomerRecord_YearlyIncome

We successfully deleted the clustered using the Drop Index statement.

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

Create Composite Clustered Index in SQL Server

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

Create SQL Server Composite Clustered Index 12

In this example, we add a cluster on the Professional and Yearly Income columns.

CREATE CLUSTERED INDEX IX_CustomerRecord_YearlyIncome
ON CustomerRecord ([Profession] ASC, [Yearly Income] DESC)
Messages
--------
Command(s) completed successfully.

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

Composite Example 14

Use Management Studio

This section covers the creation of clustered indexes using a management studio.

Use Object Explorer

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

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

create Clustered Index using SQL Server Management Studio 15

Once you choose the option, a new window will be opened. Please change the clustered index Name.

Assign Name to it 16

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

Choose the Column 17

Please change the sort order

Choose the Sort Order Ascending or descending 18

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

table Data 19

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.

Choose Indexes and Keys from Table Design 20

Please click on the Add button to add a new one. Once you select the Add option, SSMS will create it for you.

SQL Server Clustered Index Key 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 as the Sort Order.

Selecting the column name and sort order 22

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

Create a option 23

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

Table sorted in descending order 24
Categories SQL