Non Cluster Index In SQL Servers

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

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


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


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.