Index In SQL, Cluster Index, And Non-Cluster Index

What Is Index?

In General terms index is a list of keys which identifies a unique record. A simple example of Index is Book Covers index.

When we have to Read or Search any page we will check the index page first and it will make the search faster and easy. In the same way we have index in SQL, which we will use to find or search a particular record faster.

SQL Index is a database object that we can create on a column and it will help to makes search faster.

There are 2 types of Indexes:

Clustered Index
Non-Clustered Index

What Is Clustered Index?

What is Cluster?

Cluster means a group of similar things placed together. (Example Book Chapters Index and Page number.)

Clustered Index means a Unique identification of record. When we create a Primary key on any field of our SQL Table, it will create a Clustered Index on it, Primary Key can be Clustered or Non-clustered but a table can have only one cluster index on it.

It Follows a B-Tree (Binary Tree or Balanced Tree Structure) Structure. Following is the Clustered Index B-Tree Structure, where There is a Root Node, Intermediate Nodes and Leaf Node.

Root Node and Intermediate Node have an Index entry but Leaf Node has an Actual Value for Clustered Index.

How to create Clustered Index?

I have Created 2 tables [1] Category [2] Product, Now Open Category table, here I have created CategoryId as a Primary Key, once we create it as a Primary key it will automatically create Clustered Index.

To check, Right click on CategoryId and goto Indexes/Keys,

Here you can see there is already a key 'PK_Category' and Create Clustered = 'Yes' that is because CategoryId we have defined as a Primary Key.

You can check Index Name and type by executing below query.

Execute below query and click on Execution Plan, you can see here that on Category Id has Clustered Index Scan,

set statistics io on
go
select * from Category

Here, Clustered Indexed Seek means it directly fetces record and is not doing any scan, that means leaf Node has actual data.

What Is Non-Clustered Index?

Non-Clustered Index Structure is also same as Clustered Index, but the Leaf Node points to Leaf Node of Clustered Index, so basically Leaf node doesn't have actual data, instead it has a pointer or reference to Clustered Index.

It is useful for columns that have some repeated values.

A table can have multiple Non-Cluster indexes on it.

Following is the Non-Clustered Index B-Tree Structure, where There is a Root Node, Intermediate Nodes and Leaf Node. Root Node and Intermediate Nodes has Index entry but Leaf Node has Actual index for Clustered Index.

How to create Non-Clustered Index?

Now on my second table 'Product,' I have created a Non-Clustered Index by Clicking on Indexes/Keys,

Click on Add button and give a Key Name, you can see here Create as Clustered = 'No' and Columns name = 'ProductName', we have created a non-clustered index on ProductName field.

In the same way, if you want to check Index and Type, executethe below query. You can see here we have 2 Keys [1] PK_Product which is Clustered Index for Product table, and [2] NC_Product which is Non-Clustered Index.

Execute below query and click on Execution Plan, you can see here that first it does Index Scan and Then Index Seek. So first find the leaf node pointer and then find the actual value.

Difference Between Clustered Index and Non-Clustered Index

In Clustered Index, Leaf Node has Actual Data, where as Non-Clustered Index Points Leaf Node of Clustered Index.
We can have only one Clustered Index, but we can have multiple Non-Clustered Index.

When to use Clustered Index and Non-Clustered Index

Index makes search faster but it slows down the Insert - Update - Delete process. So use indexes wisely.

When we have to return a large number of result-sets, use Clustered Index and for a Query that does not return large number of result-sets but joins, use Non-Clustered Index.

Clustered Index can be used for the queries which we use many times in our project, whereas for Non-Clustered Index we have to create for those queries which are not covered by the Primary key or Clustered Index.

Hope you like this Article. Please let me know your feedback in comment section if I miseds any points.