Clustered Vs Non-Clustered Index

Introduction

 
In this article, I am going to discuss on Cluster Index and Non-Cluster Index in SQL. Database performance is very important in development and Indexes to play a very important role in it.
 
Let us start with,
 

What are Indexes in SQL?

 
Have you ever used Indexes in a book? To search topic or chapter in the book, we open indexes of the book, find the page number of a particular chapter or topic, and go directly to that topic. Just imagining, if you do not have indexes in the book and you want to search topic in the book, how difficult it would be?
 
SQL Indexes works like book index. If you do not have indexes in SQL, searching data would take more time which hampers database performance.
 

How to find which indexes created on table?

 
Assume that we have a table called “Member” and we are trying to find Indexes created on the Table using Command and SQL Server Management Studio.
  1. CREATE TABLE Member  
  2. (  
  3.     id INT PRIMARY KEY,  
  4.     firstName VARCHAR(50) NOT NULL,  
  5.     lastName VARCHAR(50) NOT NULL,  
  6.     gender VARCHAR(50) NOT NULL,  
  7.     address VARCHAR(50) NOT NULL,  
  8.     city VARCHAR(50) NOT NULL  
  9.  )  
The first way is the Using Command,
 
You can use the below command to find which indexes were created on the table.
  1. exec sp_helpindex [TableName]  
Clustered Vs Non-Clustered Index
 
This is the Inbuild store procedure that helps us to find Indexes created on the Table.
 
Another way is - Using SQL Server Management Studio,
  1.  Select table from SQL Server Management Studio
  2. Click on the Indexed folder
  3. All available indexes are visible.
eg.
 
Clustered Vs Non-Clustered Index
 

Types of Indexes in SQL?

 
Below are few important Indexes available in SQL, 
  1. Clustered
  2. Non-clustered
  3. Unique
  4. Column store
  5. Index with included columns
  6. Full-text
  7. XML
  8. Spatial etc
The Scope of this article is limited to Cluster and Non Cluster indexes.
 

Cluster Index

 
A Cluster Index determined the order of physical data of table hence table can have only one cluster index in SQL.
 
In the Cluster index, data can be reordered or sorted in oneway.
 
How to Create Cluster Index?
 
Cluster Index will be automatically created whenever we apply primary key constrain on the table column.
 
Let us follow the below steps to justify this statement.
 
First, create a member table with Primary constraint on the ID column using the below script.
  1. CREATE TABLE Member  
  2. (  
  3.     id INT PRIMARY KEY,  
  4.     firstName VARCHAR(50) NOT NULL,  
  5.     lastName VARCHAR(50) NOT NULL,  
  6.     gender VARCHAR(50) NOT NULL,  
  7.     address VARCHAR(50) NOT NULL,  
  8.     city VARCHAR(50) NOT NULL  
  9.  )  
We have set the primary key on the Id column and this will create a clustered index on the Id column automatically. You can check created index using Sp_helpindexs command or SQL Studio management Studio. I have covered that in the above topic.     2. Now we will insert data in the member table using the below script.
  1. INSERT INTO [dbo].[Member] VALUES (5,'Kirtesh','Shah','Male','Vadodara','Vadodara')  
  2. INSERT INTO [dbo].[Member] VALUES (2,'Raj','Shah','Male','Surat','Surat')  
  3. INSERT INTO [dbo].[Member] VALUES (3,'Sweta','Shah','FeMale','Mumbai','Mumbai')  
  4. INSERT INTO [dbo].[Member] VALUES (1,'Nitya','Shah','Male','Dabhoi','Dabhoi')  
  5. INSERT INTO [dbo].[Member] VALUES (4,'Hansa','Shah','FeMale','Vadodara','Vadodara')  
  6. GO  
We have inserted data not in order.
 
Fire Select Query and see data.
  1. SELECT * FROM Member  
Clustered Vs Non-Clustered Index
 
We have inserted data in a different order but when we executed the SELECT query in SQL, we notice that data is in ascending order. It means the order of physical data has changed. This is because the clustered index has maintained the order of the record based on the column. In our case ID column.
 
It is not possible to have more than one cluster index, but it is possible to have multiple columns in a single cluster index that is called a composite cluster index. We will discuss composite or Custom index below.
 
Custom or Composite Cluster index
 
We can have only one cluster index but a single Cluster index can have multiple columns which is called Composite Cluster index.
 
We will use the same table and create a custom/ composite cluster using the below command. To create own cluster index, first we have to delete previously created cluster index on table.
 
To delete existing index,
  • Select table
  • Select Indexes folder
  • Select Primary Index – Right Click – Delete. 
Clustered Vs Non-Clustered Index
 
Syntax
 
The below syntax will be used to create composite indexes.
  1. CREATE CLUSTERED INDEX IX_tblMember_Gender_FirstName  
  2. ON member(gender ASC, firstName DESC)  
The above command creates indexes on two columns
  1. gender ascending order
  2. first name descending order 
Now, will execute the select statement and examine the output. The output should be sorted in gender ascending and first name descending order.
  1. SELECT * FROM MEMBER  
Clustered Vs Non-Clustered Index
 

Non-Cluster Index

 
The non-Cluster index doesn’t sort physical data like the Cluster index. It means that the sequence of data will not be change and data will be returning on the same sequence in which its inserted.
 
Like Book, Non-Cluster index will be store in one place and data will be store in another place. This will allow creating more than one Non-cluster index on the table.
 
How to create a Non-Cluster index ?
 
It is similar to cluster index only difference is the keyword, use "Non-Cluster” instead of “Cluster ”.
 
Please find below syntax, 
  1. CREATE NONCLUSTERED INDEX IX_tblmumbai_Name  
  2. ON member(firstname ASC)  
 As mentioned, table data and index will be stored in a different location. So if you want to search the address of “Kirtesh”, it will first search the record of “Kirtesh” and the read address of searched row.
 
Differences between Cluster and Non-Cluster Index?
 
Key Differences are listed below,
 
Cluster Index
Non-Cluster Index
Can have only one Cluster Index per table.
Can have more than one Non-Cluster Index per table.
Cannot consume extra disk storage as that only sort data row in the table
Can consume more disk storage space as data and index will be store in different places.
It is faster than Non-Cluster Index.
It is slower than Cluster index as it needs extra look up to search record.
 
That's all for this article. I hope you enjoyed this article and find it useful.