Types of Index In SQL

301 View    Jan 19 2020 1:24AM

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.


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:

  1. Clustered Index
  2. Non-Clustered Index


What is Cluster?  

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


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






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.