Clustered Vs Non-Clustered Index

Introduction

This article will discuss Cluster Index and Non-Cluster Index in SQL. Database performance is very important in development, and Indexes play a very important role.

Let us start with,

What are Indexes in SQL?

Have you ever used Indexes in a book? Just imagining, if you do not have indexes in the book and want to search the topic in the book, how difficult would it be? 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.

SQL Indexes work like a 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 are created on the table?

Assume that we have a table called "Member" and are trying to find Indexes created on the table using Command and SQL Server Management Studio.

CREATE TABLE Member  
(  
    id INT PRIMARY KEY,  
    firstName VARCHAR(50) NOT NULL,  
    lastName VARCHAR(50) NOT NULL,  
    gender VARCHAR(50) NOT NULL,  
    address VARCHAR(50) NOT NULL,  
    city VARCHAR(50) NOT NULL  
 )

The first way is the Using Commands,

You can use the below command to find which indexes were created on the table.

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,

Select table from SQL Server Management Studio
Click on the Indexed folder
All available indexes are visible.

Eg.

Clustered Vs Non-Clustered Index

Types of Indexes in SQL?

Below are a few essential Indexes available in SQL,

Clustered
Non-clustered
Unique
Column store
Index with included columns
Full-text
XML
Spatial etc

The Scope of this article is limited to Cluster and Non-Cluster indexes.

Cluster Index

A Cluster Index determines the order of physical data of the table; hence, the table can have only one cluster index in SQL.

In the Cluster index, data can be reordered or sorted in one way.

How to Create Cluster Index?

Cluster Index will automatically be created when 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.

CREATE TABLE Member  
(  
    id INT PRIMARY KEY,  
    firstName VARCHAR(50) NOT NULL,  
    lastName VARCHAR(50) NOT NULL,  
    gender VARCHAR(50) NOT NULL,  
    address VARCHAR(50) NOT NULL,  
    city VARCHAR(50) NOT NULL  
 )

We have set the primary key on the Id column, which will automatically create a clustered index on the Id column. You can check created index using the Sp_helpindexs command or SQL Studio Management Studio. I have covered that in the above topic.

Now we will insert data in the member table using the below script.

INSERT INTO [dbo].[Member] VALUES (5,'Kirtesh','Shah','Male','Vadodara','Vadodara')  
INSERT INTO [dbo].[Member] VALUES (2,'Raj','Shah','Male','Surat','Surat')  
INSERT INTO [dbo].[Member] VALUES (3,'Sweta','Shah','FeMale','Mumbai','Mumbai')  
INSERT INTO [dbo].[Member] VALUES (1,'Nitya','Shah','Male','Dabhoi','Dabhoi')  
INSERT INTO [dbo].[Member] VALUES (4,'Hansa','Shah','FeMale','Vadodara','Vadodara')  
GO

We have inserted data not in order.

Fire Select Query and see data.

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 noticed that the data was 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 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 called a Composite Cluster index.

We will use the same table and create a custom/ composite cluster using the below command. First, we must delete the previously created cluster index on the table to create our cluster index.

To delete the 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.

CREATE CLUSTERED INDEX IX_tblMember_Gender_FirstName  
ON member(gender ASC, firstName DESC)

The above command creates indexes on two columns

gender in ascending order
First name in descending order

Now, we will execute the select statement and examine the output. The output should be sorted in gender ascending and first name descending order.

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 data sequence will not be changed, and data will be returned to the same sequence in which it is inserted.

Like the book, the Non-Cluster index will be stored in one place, and data will be stored in another. This will allow the creation of more than one Non-cluster index on the table.

How to create a Non-Cluster index?

It is similar to the clustered index; the only difference is the keyword, which uses "Non-Cluster" instead of "Cluster."

Please find below the syntax,

CREATE NONCLUSTERED INDEX IX_tblmumbai_Name  
ON member(firstname ASC)

As mentioned, table data and index will be stored differently. So if you want to search for the address of "Kirtesh," it will first explore the record of "Kirtesh" and the read address of searched row.

What differences between the Cluster and Non-Cluster Index?

Key Differences are listed below,

Cluster Index	Non-Cluster Index
It can have only one Cluster Index per table.	It can have more than one Non-Cluster Index per table.
It cannot consume extra disk storage as it only sorts data rows in the table.	It can consume more disk storage space as data and indexes will be stored in different places.
It is faster than Non-Cluster Index.	It is slower than the Cluster index as it needs extra look-up to search records.

Conclusion

This article taught us about Cluster Index and Non-Cluster Index and what are the differences between both with code examples in SQL. That's all for this article. I hope you enjoyed this article and found it helpful.