Non-Clustered Index in SQL Server

Non-Clustered Index in SQL Server with Examples

In this article, I am going to discuss the Non-Clustered Index in SQL Server with examples. Please read our previous article before proceeding to this article where we discussed the SQL Server Clustered Index with example. As part of this article, we are going to discuss the following pointers.

  1. What is Non-Clustered Index in SQL Server?
  2. Example to Understand the SQL Server Non-Clustered Index.
  3. Understanding the Composite Non-Clustered Index.
  4. What are the differences between a clustered and a non-clustered index in SQL Server?
  5. When should we create indexes on a table?
  6. Which Index is faster, Clustered or Non-Clustered Index?
  7. What are the different index configurations a table can have?
What is Non-Clustered Index in SQL Server?

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 non-clustered index is stored separately from the actual table. So a table can have more than one non clustered index. 

The 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.

Understanding SQL Server Non-Clustered Index:

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.

Non-Clustered Index in SQL Server

Composite Non-Clustered Index in SQL Server:

It is also also possible to create a non-clustered index based on multiple columns in SQL Server and when we do so, it is called a 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 or not by executing the following system stored procedure.

Composite Non-Clustered Index in SQL Server

What are the differences between a clustered and a non-clustered index in SQL Server?
  1. We can create only one clustered index per table whereas we can create more than one non-clustered indexes per table.
  2. 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.
  3. 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.
  4. 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 in 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:

  1. No indexes
  2. A clustered index
  3. One clustered index and many non-clustered indexes
  4. A non-clustered index
  5. Many non-clustered indexes

In the next article, I am going to discuss the Unique Index in SQL Server with examples. Here, in this article, I try to explain the SQL Server Non-Clustered Index step by step with some examples. 

Leave a Reply

Your email address will not be published. Required fields are marked *