Difference Between Clustered and Non-Clustered Index in the SQL

Introduction

Indexes are used to fasten the query process in the SQL server that results in high performance. They are quite similar to the textbook indexes. In a textbook, to open a particular chapter or topic, you first go to the index, and find the name of chapter or topic then jump to the desired page directly. Without indexes in the textbook, finding a specific topic in the textbook would be very slow.

The same concept is applied to the database indexes as well. If there are no indexes in the SQL then DBMS has to go through all the records in a table to retrieve the desired results. In this case, the process of scanning the desired records is extremely slow. On the other hand, when you create indexes, the DBMS goes to that index first and retrieves the corresponding record directly without wasting any time into other things.

There are two common types of indexes in the SQL server, these are the Clustered Index and the non-clustered index. In this blog, we will discuss the difference between clustered and non clustered index in detail. Before proceeding the difference between clustered and nonclustered index in SQL Server, we’ll first brief each of them first.

What are clustered indexes in the SQL Server?

Every table in the database has a natural order. If the order is random and it is not defined explicitly then the table is known as the heap here. Except for the few cases, heaps are not considered as a good programming practice because the SQL server has no meta-knowledge about how data is stored within a heap. If there is not a random heap, it means table data is stored in some order and the physical storage of data is defined by the clustered indexes in SQL. Every table can have only one clustered index because data can be stored in a single order only. It signifies that data in a table stored physically on the disk cannot have more than one order.

Example of Clustered Indexes

Take the example of the “Student_info” table to understand the practical aspects of clustered indexing in the SQL server. This table contains “Roll_No” as the primary key and the clustered index is self-created here on the primary key that will sort the “student_info” table as per the “Roll_No”. In other words, clustered indexing works as the dictionary where sorting order is alphabetical and there is no need for defining any additional index page.

Input:
CREATE TABLE Student_info
(
ROLL_NO int(10) primary key,
NAME varchar(20),
DEPARTMENT varchar(20),
);
insert into Student_info values(1410110405, 'H Agarwal', 'CSE') 
insert into Student_info values(1410110404, 'S Samadder', 'CSE')
insert into Student_info values(1410110403, 'MD Irfan', 'CSE') 
SELECT * FROM Student_info 

Output for this query will be:

ROLL_NO	NAME	DEPARTMENT
1410110403	MD Irfan	CSE
1410110404	S Samadder	CSE
1410110405	H Agarwal	CSE

If you want to define clustered indexes for other columns then first you should remove the primary key then you can remove the first index too. Keep in mind that defining a column as the primary key will make that column the clustered index of that table. To set any other column as the clustered index, we should remove the previous one with the following procedure:

Read: SQL Data Types for Oracle PL/SQL, MySQL, SQL Server, and MS Access

Table:Student_info

ROLL_NO	NAME	DEPARTMENT
1410110405	H Agarwal	CSE
1410110404	S Samadder	CSE
1410110403	MD Irfan	CSE

Output: Index

NAME	ROW_ADDRESS
H Agarwal	1
MD Irfan	3
S Samadder	2

Read: What is Primary Key in SQL? How to Add, Remove, Or Set Primary Key

What are the benefits of a clustered index?

• Finding the desired data in the clustered index is a matter of knowing where to look in our alphabetical list of data.
• If you need the output in the same order then it is stored in Presto, and there is no additional sorting required later.
• It is usually fine putting the clustered index on the primary key of a table because the primary key is generally the most used field for joins, where statements etc.
• If you don’t want to put the primary key as the clustered index then drop the previous index and define it again as required.
• One major benefit of the clustered index is that actions like insert and updates become faster because SQL doesn’t have to put data in sorted order any more.
• Another common benefit of the clustered index is that we don’t have to include any additional data in our index. It is necessarily not true for other types of indexes like non-clustered indexes.
• Pretend the clustered indexes like a phonebook where all contacts are arranged in alphabetical order and it is easy to find the desired one without performing any additional research.

What are nonclustered indexes in the SQL Server?

A nonclustered index contains the ordered data for the columns specified in that index with pointers that will guide where to find the rest of the data from that row. The major difference between clustered index and nonclustered index is that  nonclustered indexes are a two-step process where you should first the value of interest then go and look up the rest of the data of that row where it actually exists on the disk. The non-clustered index is an index structure separate from the data stored in the table that renders one or more selected columns. The non-clustered index is created to improve the performance of frequently used queries that are not managed well by clustered indexes. It is like a notebook where the index page is put separately at the beginning of the book.

Example of nonclustered indexes:

Input: 
CREATE TABLE Student_info
(
ROLL_NO int(10),
NAME varchar(20),
DEPARTMENT varchar(20),
);
insert into Student_info values(1410110405, 'H Agarwal', 'CSE') 
insert into Student_info values(1410110404, 'S Samadder', 'CSE')
insert into Student_info values(1410110403, 'MD Irfan', 'CSE')

SELECT * FROM Student_info 

Output for this query will be:

ROLL_NO	NAME	DEPARTMENT
1410110405	H Agarwal	CSE
1410110404	S Samadder	CSE
1410110403	MD Irfan	CSE

Keep in mind that it is possible creating multiple nonclustered indexes for the database tables while the clustered index is exactly one for every database table. Here is how to define nonclustered indexes in the SQL server –

Read: Know Everything About a SQL Developer Salary for Freshers & Experienced Professionals in 2021!

Input: 
//Create Non-Clustered index
create NonClustered index IX_table_name_column_name 
      on table_name (column_name ASC)  

Output: Table:Student_info

ROLL_NO	NAME	DEPARTMENT
1410110405	H Agarwal	CSE
1410110404	S Samadder	CSE
1410110403	MD Irfan	CSE

Input: create NonClustered index IX_Student_info_NAME on Student_info (NAME ASC) Output: Index

NAME	ROW_ADDRESS
H Agarwal	1
MD Irfan	3
S Samadder	2

What are the benefits of a nonclustered index?

• We can define more than one nonclustered indexes on our tables as we want. It is really great creating a separate index for every column.
• You get the speed and efficiency in data lookup but with the cost of losing the disk space by creating nonclustered indexes. Sometimes the creation of unnecessary indexes may disturb the performance cycles badly, so test your changes closely.
• Using non clustered indexes to find the desired data is preferable and computers are really good at using this process to look up the data.
• Non clustered indexes improve the performance because you don’t have to go somewhere else to find the data but you lose storage space here by creating supplicate values as the part of your index.

Read: How to Become a SQL Database Administrator?

Example Usage Scenarios

If there is an OLTP table that is used for transactional reads and writing new rows. The primary key is the major identifier here. So, what type of index you should use for the primary key here?

It is recommended using the clustered index here when the primary key is the major identifier for the table. If you store the data in the same format as arranged by the primary key then SQL can read data very quickly. If there is a query that you want to return most or all the columns from a table then what type of index is suitable in this case?

Read: Most Popular SQL Server Performance Tuning Tips

The clustered index is suitable here because all columns are stored in the same order they are sorted by the primary key in the clustered indexing process.

There is a table whose values change constantly and those updated values are used in Join and Where clauses. So, which type of indexing suits most here?

Nonclustered indexes are more suitable for tables whose values change frequently.

Final Words:

From the discussion, it is clear how clustered and nonclustered indexes are different. Let’s summarize here the difference between clustered and nonclustered indexes. 

• There can be only one clustered index per table while non clustered tables can have multiple indexes.
• Clustered indexes sort data and don’t require any additional extra space while nonclustered indexes are stored in a separate table from the actual and demands for more storage space.
• The difference between clustered and non clustered index is that Clustered indexes are usually faster as they don’t demand the extra look space.

It is very important for you to know these differences because these days the difference between non clustered and nonclustered columnstore index is one of the most frequently asked questions of an interview related to any SQL related job profile. What is the  For more information on SQL indexing and its usage, join the SQL certification course online at JanBask Training and master the database concepts thoroughly.

Read: What Is The Difference Between Having And Where Clause In SQL?

FaceBook Twitter Google+ LinkedIn Pinterest Email