Cluster Index In SQL Server

Introduction

This article explains about index in SQL Server in a very simple way. Many freshers and students are struggling to understand about index in SQL Server. In this article the concept of index is explained with examples.

Definition

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

Real Time Example

Train Ticket Confirmation System is a good example of index because the confirmation details have coach number, seat number, and berth details. Using the given details, we can find our seat faster and more easily. If we do not have these details, we need to check each coach, which will take more time.



Types of Index

There are two types of index in SQL Server.

1. Cluster Index
2. Non-cluster index

Syntax

Create [Type of index] [Index Name] On [Table Name] (fields for index)

Explanation

1. “Create” and “On” are keywords in SQL Server.
2. [Type of Index]- the type of index we need to create for our requirements.
3. [Index Name] is an index name which is given by user. We give index name related to the field name and table name.
4. [Table Name] (Fields for index) - table and its fields which are used to create index.

Syntax Diagram



Cluster Index

Cluster index is one of the SQL objects. We can create only one cluster index in a table. Cluster index is stored in memory physically, in binary tree format. We can divide Cluster index into the following types.

1. Cluster Index
2. Unique Cluster index
3. Composite Cluster Index
4. Unique Composite cluster index

Cluster Index

We can create cluster index of fields or columns in a table. We can use the following method to create an index. This kind of index accepts duplicate values.

Example Query

CREATE CLUSTERED INDEX IX_Test_Id ON test(Id)



After creating Index, enter the value. At the same time, we can enter the same id (duplicate) in this index.



Unique Cluster Index

Unique cluster index accepts unique values only. It does not accept duplicate values.

Example Query

CREATE UNIQUE CLUSTERED INDEX IX_Unique_Test_Id ON test(Id)



After creating unique cluster index, we cannot insert duplicate values in table which field created unique index. First, we insert two records into “Test” table. Then, when we again insert the same Id into table, we get the error as in the below screenshot.



Note - We can create Unique cluster index after inserting records. If we have duplicate records in table, we get an error while creating unique index cluster index, like the below screenshot.



The above table has duplicate records. So, if we try to create Unique clustered index, we will be getting the below error.



Composite Cluster Index

When we create an index that is a combination of more than one column name in a table or more than one field, it is called Composite cluster index. We can insert duplicate values into table also in Composite cluster index.

Example Query

CREATE CLUSTERED INDEX IX_Test_Id ON test(Id,Name)

We created index for the combination of two columns - one for “Id” and another one for “Name”.



In our table, we have already inserted the records. There are two records in our table, as shown below.

Composite cluster index accepts duplicate records, as shown in the below screenshot.



Unique Composite cluster index

Unique cluster index is a combination of Unique and Composite cluster indexes. It does not accept duplicate vales.

Example

CREATE UNIQUE CLUSTERED INDEX IX_Test_Id ON test(Id,Name)



After creating unique composite cluster index, we can not enter duplicate values with the combination of two columns. For example, we insert two records with different values. But when we try to insert the same Id and name again, we are getting error.

Note
We can create index after inserted records to a table. If table have duplicate records we get error while creating index, because unique composite cluster index does not accept duplicate records.



Above Screen shots shows duplicate records. If creating a unique cluster composite gets an  error  it looks like below screen shots.



Way of checking Index in table

We can find the index name and the number of indexes created in a table in SQL Server.

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


Conclusion

This article explains about cluster index in a simple way. I hope it is very helpful to students and freshers. The next article will explain about non cluster index in SQL server.