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 where we discussed the basics of SQL Server Indexes and how we can improve the search operations using indexes. As part of this article, we are going to discuss the following pointer.
- Understanding the B-Tree structure of Clustered Index
- What is a Clustered Index?
- Why a table can have only one Clustered Index?
- Example to understand the Clustered Index.
- Understanding the SQL Server Composite Clustered Index.
Understanding the B-Tree structure of Clustered Index:
The first thing is always first. So, let us first understand the Balanced Tree (B-Tree) structure of the clustered index. Please have a look at the following diagram which shows the B-Tree structure of the Clustered Index. As you can see in the below image, the leaf nodes contain the actual data. So, when we search for any data, the clustered directly gets the data from the leaf node.
What is SQL Server Clustered index?
The Clustered Index in SQL Server defines the order in which the data is physically stored in a table. That is the leaf node store the actual data. As the leaf nodes store the actual data a table can have only one clustered index.
The Clustered Index by default created when you created the primary key constraint for that table. That means the primary key column creates a clustered index by default.
When a table has a clustered index then the table is called a clustered table. If a table has no clustered index its data rows are stored in an unordered structure.
Understanding the Clustered Index with an example:
In order to create to understand the clustered index and to make sure that the clustered index by default created when we created the primary key constraint on a table, please execute the following query.
CREATE TABLE Employee ( Id INT PRIMARY KEY, Name VARCHAR(50), Salary INT, Gender VARCHAR(10), City VARCHAR(50), Dept VARCHAR(50) ) GO
As you can see in the above query, we created the Employee table having the Id column as the Primary key column. The Primary key constraints in SQL Server by default creates a clustered indexes 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
Execute sp_helpindex Employee
Once you execute any of the above queries it will give the following output. Here, you can find the index name, type of index as index_description and the column on which the index is created as index_keys.
Or alternatively, you can check the below folder.
Now insert some records in random order into the Employee table. Notice the values of the Id column of the Employee table is not in 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 Fetch the data by executing the following query.
SELECT * FROM Employee;
When we execute the above Select query it will give us the below output.
Notice, the records are sorted in the table in ascending order based on the Id column. This is because of the Clustered Index which we created on the Id column. The job of Clustered Index in SQL Server is to determine or organized data in either ascending or descending order on the column(s) on which it is created.
Can we create multiple clustered index in a table in SQL Server?
It is not possible to create more than one clustered index in a table in SQL Server. The Employee table that we have worked so far has one clustered Index on the Id column which already determines the physical storage order of the data in the table. Let us try to create another clustered Index on the Salary column and see what happens. Let us proof this by executing the following SQL Query.
CREATE CLUSTERED INDEX IX_Employee_Salary ON Employee(Salary)
When we try to execute the above SQL Script, it throws the following error. This is because we already have a clustered index that already arranged the data in the table. This is the reason why a table can have only one clustered index.
Cannot create more than one clustered index on table ‘Employee’. Drop the existing clustered index ‘PK__Employee__3214EC07DCDC2006’ before creating another.
What is Composite Clustered Index in SQL Server?
It is possible in SQL Server to create the clustered index with multiple columns and if we do so, then it is called a composite clustered index.
Let’s create a clustered index based on 2 columns. To do this we first have to drop the existing clustered index which is created on the Id column.
DROP INDEX Employee.PK__Employee__3214EC07AED992AA
When you execute the above DROP INDEX query, then you will get an error message stating
An explicit DROP INDEX is not allowed on index ‘Employee.PK__Employee__3214EC07DCDC2006’. It is being used for PRIMARY KEY constraint enforcement.
Why it is not allowing that we will discuss in a later article. But for now to 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)
Fetching the data:
Now issue the following select query to fetch the data,
SELECT * FROM Employee;
When you execute the above select query, you will get the following data. Now, you can see the data FIRST arranged by the Gender in descending order and then by Salary in ascending order.
In the next article, I am going to discuss the Non-Clustered Index in SQL Server with some examples. Here, in this article, I try to explain the Clustered Index in SQL Server step by step with some examples.