SQL Server Non-Clustered Index with Real-time Examples
In this article, I will discuss SQL Server Non-Clustered Index with examples. Please read our previous article before proceeding to this article where we discussed the SQL Server Clustered Index with an example. In the case of SQL Server Non-Clustered Index, the arrangement of data in the index table will be different from the arrangement of data in the actual table.
The SQL Server non-clustered index is analogous to an index of a textbook. That means the data is stored in one place and the index is stored in another place. Moreover, the index will have pointers to the storage location of the actual data.
The non-clustered index in SQL Server is stored separately from the actual data. So a table in SQL Server can have more than one non clustered index, just like how a book can have an index by chapters at the beginning and another index by common terms at the end of the book.
In the index table, the data is stored either in the ascending or descending order of the index key which does not make any effect or changes to the actual data stored in the table.
Let us understand this with an example.
First, Create the Employee table by using the following SQL Script
CREATE TABLE Employee ( Id INT PRIMARY KEY, Name VARCHAR(50), Salary INT, Gender VARCHAR(10), City VARCHAR(50), Dept VARCHAR(50) ) GO
At the moment when we execute the above SQL Script, it will create one clustered index on the Id column of the Employee table.
Now insert some data into the Employee table by using the following SQL Script.
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') GO
Now if you see in the Employee table, the data get stored in the ascending order of the Id column. This is because of the clustered index which is created on the Id column.
Now let’s turn our attention towards the SQL Server Non-Clustered Index. Now we will create one Non-Clustered Index on the Name column of the Employee table. To do so, use the following SQL Script.
CREATE NONCLUSTERED INDEX IX_Employee_Name ON Employee(Name ASC)
Once you execute the above SQL Script, a Non-Clustered Index is created on the Name column of the Employee table. If you want to verify whether the non-clustered index is created or not, you can use the following system-defined stored procedure i.e. sp_helpindex as shown below
EXEC sp_Helpindex Employee
Now when we execute the above system-defined stored procedure it gives us the information about all the available indexes on the Employee table as shown below.
Composite SQL Server Non-Clustered Index:
We can also create a non-clustered index in SQL Server based on multiple columns and when we do so, it is called as Composite Non-Clustered Index.
Let’s create an Index based on the Gender in Ascending order and Salary in Descending Order.
CREATE NONCLUSTERED INDEX IX_Employee_Gender_Salary ON Employee(Gender ASC, Salary DESC)
Once you execute the above Create Index query, it will create the composite non-clustered index. Let’s verify whether the index is created on not by executing the following system stored procedure.
What are the differences between a clustered and a non-clustered index in SQL Server?
We can create only one clustered index per table whereas we can create more than one non-clustered indexes per table.
The SQL Server Clustered Index determines the storage order of the rows in the table. Hence does not require additional disk space whereas a Non-Clustered Index in SQL Server is stored separately from the table. So it required additional storage space.
The clustered index in SQL Server is a special type of index which will physically reorder the data in the table. This is the reason why a table can have only one clustered index in SQL Server.
The SQL Server non-clustered index is stored separately from the actual data. As a result, the logical order of the index does not match the physical order of the data stored in the table.
When should we create indexes on a table?
We need to create an index on the columns of a table provided those columns are frequently used in where clause condition or in the order by clause.
It is not advised creating an index on each and every column of a table because more number of indexes can degrade the performance of the database. The reason is with every modification we make on the data should be reflected into all the indexes of the table.
Which Index is faster, Clustered or Non-Clustered Index?
In SQL Server, the Clustered Index is slightly faster than the Non-Clustered Index. This is because when a Non Clustered Index is used there is an extra look up from the Non-Clustered Index to the actual table to fetch the actual data.
What are the different index configurations a table can have?
A table can have one of the following index configurations:
- No indexes
- A clustered index
- One clustered index and many non-clustered indexes
- A non-clustered index
- Many non-clustered indexes
In the next article, I will discuss the Unique Index in SQL Server with examples.
In this article, I try to explain the SQL Server Non-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.