Clustered Index in SQL Server with Examples
In this article, I am going to discuss the Clustered Index in SQL Server with examples. Please read our previous article before proceeding to this article where we discussed the basics of SQL Server Indexes. As part of this article, we are going to discuss the following pointers.
- What is a Clustered Index?
- Why a table can have only one Clustered Index?
- Example to understand SQL Server Clustered Index.
- Understanding the SQL Server Composite Clustered Index.
What is a Clustered Index in SQL Server?
The Clustered Index in SQL Server 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.
Why a table can have only one Clustered Index?
The Clustered Index in SQL Server determines the physical order of data in a table. This is the reason why a table can have only one clustered index. When the table has a clustered index then the table is called a clustered table. If the table does not have a clustered index then the data is not going to be stored 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 marked the Id column of the above Employee table 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 as SELECT * FROM Employee which will give us the below output.
Understanding the Clustered Index:
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. 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.
Composite Clustered Index in SQL Server:
To create a Clustered Index 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 you 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 you issue a select query against the Employee table you should see the data physically arranged, FIRST by the Gender in descending order and then by the Salary in ascending order. as shown below.
In the next article, I will discuss the Non-Clustered Index in SQL Server with examples. Here, in this article, I try to explain the SQL Server Clustered Index step by step with some examples.