Cluster Index In SQL Server

Vignesh Mani

SQL ServerIndex in SQL Server

7 Comments

Join the conversation! Your thoughts help the community grow.

Vignesh Mani9y ago
Thank you so much your feedback !!.
Pankaj Gupta10y ago
Nice article with good explanation.
Pradeep Sahoo10y ago
Nice article .thanks for sharing
Nilkamal Shah10y ago
It will be good if you can explain with train example and also demonstrate how it will work behind the scene.
- Manish Kumar10y ago
  HI Nilkamal, Here are scene behind indexes. As we know we can have only one clustered index in a table and clustered index sort data so we can get data very fast (as things are sorted so its very easy to find a particular data). Also for sorting, sql server assigning serial number/ row number ( in layman term ) internally. Clustered index follows B-Tree structure means one parent then child then child of child and data is saved at leaf node. Now for non clustered index it follows clustered index address means it will not store data at leaf node but there is pointer at leaf node towards clustered index. Further if there is no clustered index on a table and we have non clustered index then pointer will become RID look up (please get more idea on this.) Thanks
- Manish Kumar10y ago
  Also there are lot of things in index like index fragmentation, rebuild and reorganize of index, types of non clustered index and in sql server 2014 on wards there is new index column store index where data is stored column wise unlike traditional index where data is stored in row wise.
Anu V10y ago
Nice article.. Thanks for sharing...

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.

Cluster Index
Non-cluster index

Syntax

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

Explanation

“Create” and “On” are keywords in SQL Server.
[Type of Index]- the type of index we need to create for our requirements.
[Index Name] is an index name which is given by user. We give index name related to the field name and table name.
[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.

Cluster Index
Unique Cluster index
Composite Cluster Index
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.