SQL Server Clustered Index with Real-time Examples
In this article, I am going discuss the SQL Server Clustered Index with examples. Please read our previous article before proceeding to this article where we discussed the basics of SQL Server Indexes. The SQL Server Clustered Index defines the order in which the data is physically stored in a table.
In SQL Server, a table can have only one clustered index which will be created by default when we created the primary key constraint for that table. That means the primary key constraints by default creates a clustered index in SQL Server.
The SQL Server Clustered Index determines the physical order of data in a table. This is the reason why a table can have only one clustered index in SQL Server.
When the table has a clustered index then the table is called as a clustered table. If the table does not have a clustered index then the data is not going to be stored in the table in sorted order.
Let us understand SQL Server Clustered Index with an example:
First, create the following Employee table
CREATE TABLE Employee ( Id INT PRIMARY KEY, Name VARCHAR(50), Salary INT, Gender VARCHAR(10), City VARCHAR(50), Dept VARCHAR(50) ) GO
Here we have to notice that the Id column of the above Employee table is marked as the Primary key. The Primary key constraints in SQL Server by default creates a clustered indexes automatically if there is no clustered index already exists on the table.
To Confirm whether the Clustered index is created or not execute the following code
sp_helpindex tblEmployee or Execute sp_helpindex tblEmployee
Or alternatively, you can check the following folder in SSMS
Now we need to insert some records into the Employee table and here we need to notice that the values of the Id column of the Employee table are not in a sequential order
INSERT INTO Employee VALUES (3,'Pranaya', 4500, 'Male', 'New York', 'IT') INSERT INTO Employee VALUES (1,'Anurag', 2500, 'Male', 'London', 'IT') INSERT INTO Employee VALUES (4,'Priyanka', 5500, 'Female', 'Tokiyo', 'HR') INSERT INTO Employee VALUES (5,'Sambit', 3000, 'Male', 'Toronto', 'IT') INSERT INTO Employee VALUES (7,'Preety', 6500, 'Female', 'Mumbai', 'HR') INSERT INTO Employee VALUES (6,'Tarun', 4000, 'Male', 'Delhi', 'IT') INSERT INTO Employee VALUES (2,'Hina', 500, 'Female', 'Sydney', 'HR')
Now view the table data
SELECT * FROM Employee
When we execute the above Select query it will give us the below output.
We insert the records into the Employee table in random order. But when we execute the select query, we get the data in ascending order of the Id column which is nothing but the primary key column. The Primary Key constraints by default create a clustered index in SQL Server. So here the Clustered Index is created on the Id column of the Employee table. Then the Clustered Index determines the physical order of the data in a table. So here the data is sorted based on the Id column.
As we already discussed that a clustered index determines the physical storage order of the data in a table. So a table can have only one clustered index in SQL Server. If we take the example of the above Employee table the data is already arranged by the Id column. Now, if we try to create another clustered index on the Salary column the data needs to be rearranged based on the Salary column which will affect the ordering of rows that’s already been sorted based on the ID column.
This is the reason why SQL Server doesn’t allow us to create more than one clustered index per table. The following SQL Script raises an error stating Cannot create more than one clustered index on table ‘Employee’. Drop the existing clustered index ‘PK__Employee__3214EC07DCDC2006’ before creating another.
CREATE CLUSTERED INDEX IX_Employee_Salary ON Employee(Salary)
We just discussed that a table can contain only one clustered index in SQL Server. However, that clustered index can contain multiple columns and if we do so then is also called a composite index.
Let’s now create a clustered index based on 2 columns.
To create a Clustered Index in SQL Server based on two columns, first, we need to drop the existing clustered index which is created on the Id column of the Employee table. Use the following DROP Index statement to drop the index.
DROP INDEX Employee.PK__Employee__3214EC07DCDC2006
When we execute the above DROP INDEX query, we get an error message stating
We will discuss the role of the unique index in our upcoming articles. To successfully delete the clustered index, right click on the index in the Object Explorer window and select DELETE as shown below.
Now, execute the following CREATE INDEX query to create a composite clustered Index on the Gender and Salary columns.
CREATE CLUSTERED INDEX IX_Employee_Gender_Salary
ON Employee(Gender DESC, Salary ASC)
Now if we issue a select query against the Employee table we should see the data physically arranged, FIRST by the Gender in descending order and then by the Salary in ascending order. The data will be shown like below when we issue the Select Query.
In the next article, I will discuss the Non-Clustered Index in SQL Server with examples.
In this article, I try to explain the SQL Server Clustered Index step by step with some examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.