Non Cluster Index In SQL Servers

Introduction

This article explains about Non Cluster Index in SQL Server. Many freshers and students are struggling to learn about index in SQL Server. So, this article explains about Index with simple examples. Before reading this article, please refer to the previous part of this article at the below link.

• Cluster Index In SQL Server

Definition

Index is an SQL Object. It is a data structure that improves the speed of data retrieval operations. Index is one of the query optimizers and it is used to increase the performance.

There are  the following types of indexes in SQL Server.

1. Cluster Index
2. Non Cluster Index

Non Cluster Index

We often use this Index in SQL Server. We can create more than one non cluster index in a table. Non cluster indexes are stored in memory logically. Non cluster index is stored as a binary tree format. Non cluster index will be stored in order based on index key value. We can divide Non Cluster Index into  the following types:

1. Non Cluster Index
2. Unique Non Cluster Index
3. Composite Non Cluster Index
4. Unique Composite Non Cluster Index

Non Cluster Index

We can create non cluster index to fields or columns in tables. We can create the following way to create a non cluster index. This is will accept duplicate values.

Example Query

CREATE NONCLUSTERED INDEX IX_Person_FirstName ON Person(FirstName)

We are creating Non Cluster Index for FirstName columns in the “Person” table. After creating we can enter values and at the same can enter duplicates into table. The below screen shots show how we can enter duplicate values in Non Cluster Index




We can see which index we created for our table. Open object ,explore and go to database, then go to corresponding table under database. Now expand the table, then expand index under the table. Below screen shot shows index name and type of index name. Non Cluster Index is non-unique.




We can create more than one Non Cluster Index in a table with a different index name. Below screen shot shows how we can create more than one Non Cluster Index in a table.




If we try to create a Non Cluster Index with the same name we will get an  error. When we move the cursor on the index name we can see the alert message like the below screen shot.



Unique Non Cluster Index

Unique Non Cluster Index only accepts unique values. It does not accept duplicate values.

Example Query

CREATE UNIQUE NONCLUSTERED INDEX IX_Person_FirstName ON Person(FirstName)

After creating a unique Non Cluster Index, we cannot insert duplicate values in the table.


First we insert three records into “Person” table and it will be executed without error.



Now again insert the already -nserted first name value in FirstName column into table, it gets an error as in the below screen shot.



Note

We can create a unique Non Cluster Index after inserting records in a table. If we have duplicate records in the table, we will get an error while creating it. 

 

Composite Non Cluster Index

Creating a Non Cluster Index combination of more than one column name in a table or more than one field is called Composite Non Cluster Index. We can insert duplicate values into a table if we also create a Composite Cluster Index.

Example Query

CREATE NONCLUSTERED INDEX IX_Person_FirstLastName ON Person(FirstName,LastName)

We created a Non Cluster Index for a combination of two columns; one for “FirstName,” and another one for “LastName” in Person table. This is called Composite Cluster Index.

Composite Non Cluster Index accepts duplicate records,  for example see the below screen shots.



Unique Composite non cluster index

Unique Composite Non Cluster Index is a combination of unique and composite non cluster indexes. It does not accept duplicate vales.

Example

CREATE UNIQUE NONCLUSTERED INDEX IX_Person_FirstLastName ON Person(FirstName,LastName)

After creating unique Composite Non Cluster Index we cannot enter duplicate values with a combination of two columns. For example we insert three records with different values, then the fourth one tries to insert the same “FirstName” and “LastName” but it gets an error because of the unique Composite Non Cluster Index.

Below screen shots explains about unique Composite Non Cluster Index. Three records are inserted successfully. When a fourth record is inserted with the same value it will get an  error.



Note

We can create a unique Composite Non Cluster Index after inserting records into a table. If the table has duplicate records we will get an error while creating unique Composite Non Cluster Index, because unique Composite Non Cluster Index does not accept duplicate records.

Way of checking Non Cluster Index in table

We can find the index name and how many indexes have been created to a table in SQL server.

Go to Object Explore - Database - Table - Table_Name - Indexes - Index names and corresponding fields.




Conclusion

This article explains about Non Cluster Index in a simple way. I hope it is very helpful to students and freshers.  The next article will explain about  the internal structure of Cluster and Non Cluster Indexes in SQL server.