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, clustered index will sort the records first and then store them. Generally, when you create Primary Key then the Clustered index will automatically created by that primary key. However, you can explicitly create a clustered index using the CREATE CLUSTER INDEX statement.

TIP : You can create only One Clustered Index per Table.

In this article we will show you, How to create and delete (drop) a clustered index in SQL Server using Transact SQL Query, and SQL Server Management Studio

Create Clustered Index in SQL Server

When you create Primary Key Constraint on a column then SQL will automatically create a Clustered Index on that column for you. This will happen only if there is no Clustered Index in that 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. I Suggest you to refer Create Table article to understand the Create Table statement.

OUTPUT

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

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

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

This will show all the information about the table.

Or you can simply expand the table on which we created the Clustered Index, and expand the Indexes folder as we shown below

Insert rows into Clustered Index Column

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

OUTPUT

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

Delete Clustered Index in SQL Server

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

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

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 will open the context menu. Please select the Delete option

Once you select the Delete option, Delete Object window will be opened. Click OK to delete the Index.

Create SQL Clustered Index on Existing table

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

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

Now you can see that the data is sorted by the Yearly Income column in ascending order

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

Now you can see we successfully deleted the clustered index using Drop Index statement.

Create Composite Clustered Index in SQL Server

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 same we added two more records to the Customer record table

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

OUTPUT

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

Create Clustered Index using SQL Server Management Studio

Use Object Explorer to Create Clustered Index in SQL Server

In this example we will show you, How to create a Clustered Index using the SQL server 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 Clustered Index.. option as shown below

Once you select Clustered Index.. option,  a new window called New Index will be opened as we shown below. Please change the Index Name as per your requirements

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

Please change the sort order

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

Create SQL Clustered Index using Table Designer

Please expand the table on which you want to create a Clustered Index, and Right click on it will open the context menu. Please select Design option to open the table in Designer window. Next, Right click on the column will open the context menu. Please select the Set Indexes / Keys… option as we shown below.

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

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.

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

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

Thank You for Visiting Our Blog