What are the advantages and disadvantages of the Clustered index?

Advantages:These indexes are ideal for range with maximum, minimum, or count type queries.For searching, we can go straight to a specific point in data to keep reading sequentially from there.It helps to minimize the page transfers and also maximize the cache hits.Disadvantages:Lots of insert operations are there in non-sequential order.There will be extra work for insert, update, and delete operations in SQL.

What are the advantages and disadvantages of the Non-clustered index?

Advantages of Non clustered index:It helps to retrieve the data from the database table quickly.Using this, we can avoid the overhead cost associated with the clustered index.Since we can create one more Non clustered index in a table. It can be used to create more than one index.Disadvantages of using Non clustered index:We can not sort the data rows physically in a non clustered index.The lookup process on Non clustered index is costly.

What are the key differences between clustered and non clustered indexes?

The main differences between them are as follows:Cluster index sorts the data rows in the table according to their key values, while Non clustered index stores the data at one location and indices at another location.Cluster index doesn't need extra disk space, whereas Non clustered index needs extra disk space.

Table of contents

Introduction

1.1.

Do you know, What is an Index?

1.2.

Types of Indexes

Clustered Index

2.1.

Example of Clustered index

Non-Clustered Index

3.1.

Example of Non clustered index

FAQs

4.1.

4.2.

Key Takeaways

Last Updated: Mar 27, 2024

SQL Queries On The Clustered And Non-clustered Indexes

Author Prashant Singh

Introduction

Before we start this, do you know what indexes are?

You must have used indexes in a book! To search any topic or chapter, we search that topic in the indexes of the book, find the page number of that topic, and go directly to that topic without checking every page before it. Just imagine this, how difficult it would be if we don't have indexes, and we have to search every page before getting to that desired topic 😱!

Indexes in SQL works like book indexes. If we don't have indexes in SQL, searching the data would be challenging and time-consuming, decreasing the performance of the database.

Read About - Specialization and Generalization in DBMS and Recursive Relationship in DBMS

Do you know, What is an Index?

An index is a key built from one or more than one column in the database, which speeds up fetching or searching rows from the table (or data from the database).

Types of Indexes

Some of the types of indexes in SQL

are:

Clustered Index
Non-clustered Index
Unique Index
Column Store Index
Full-text Index
XML Index
Index with included columns etc.

Among these, Clustered and Non clustered indexes are the majorly used ones, Now we will learn about Clustered and Non clustered indexes in detail.

Recommended Topic, Schema in DBMS and Checkpoint in DBMS.

Clustered Index

It is a type of index which sorts the data rows in the table according to their key values. In the database, there exists only one clustered index per table.

A clustered index defines the order in which data is stored in the table, sorted only in one way, either increasing or decreasing.

Example of Clustered index

Let us understand it using an example.

Suppose we have a table USER_INFO which contains UserID as a primary key that is self-created. On the basis of this primary key, the USER_INFO table will be sorted as per UserID.

The clustered index is like a dictionary, where sorting order is alphabetical, so a separate index is not needed anymore.

Below is the syntax for creating the above USER_INFO table:

CREATE TABLE USER_INFO(

UserID int(10) primary key,

UserName varchar(20),

Location varchar(20),

);

Insert into USER_INFO values(105, ‘Ayush Agarwal’ , ‘Lucknow’);

Insert into USER_INFO values(102, ‘Neha Singh’, ‘Pune’);

Insert into USER_INFO values(106, ‘Aman Kumar’, ‘Delhi’);

Insert into USER_INFO values(104, ‘Sumit Pal’, ‘Pune’);

Insert into USER_INFO values(107, ‘Divyansh’, ‘Delhi’);

Insert into USER_INFO values(103, ‘Shreyanshi’, ‘Kanpur’);

Insert into USER_INFO values(101, ‘Vivek Goswami’, ‘Noida’);

SELECT * From USER_INFO;

The output table “USER_INFO” for the above query will be:

UserID	UserName	Location
101	Vivek Goswami	Noida
102	Neha Singh	Pune
103	Shreyanshi	Kanpur
104	Sumit Pal	Pune
105	Ayush Agarwal	Lucknow
106	Aman Kumar	Delhi
107	Divyansh	Delhi

To create a clustered index on the other column, we must first remove the primary key to remove the previous index.

Note: Defining a column as a primary key makes that column the table’s clustered index.

Below is the syntax for removing the previous index to make any other column the clustered index.

Drop index table_name.index_name;

Create Clustered index IX_table_name_column_name on table_name (column_name ASC);

This is the general syntax to create a clustered index from any other column.

Must Read SQL Clauses

Non-Clustered Index

It is an index structure separate from the data stored in the table, and it reorders one or more than one selected column. The non clustered index is created to improve the performance, especially for frequently used queries. It is like a textbook; the index page is created separately at the beginning of that textbook.

Note: We can create one more Non clustered index in a table.

Example of Non clustered index

Let us understand it using an example.

Suppose we have a table USER_INFO which contains UserID as a primary key that is self-created. Based on this primary key, the USER_INFO table will be sorted as per UserID.

The clustered index is like a dictionary, where sorting order is alphabetical, so a separate index is not needed anymore.

Below is the syntax for creating the above USER_INFO table:

CREATE TABLE USER_INFO(

UserID int(10) primary key,

UserName varchar(20),

Location varchar(20),

);

Insert into USER_INFO values(101, ‘Vivek Goswami’, ‘Noida’);

Insert into USER_INFO values(102, ‘Neha Singh’, ‘Pune’);

Insert into USER_INFO values(103, ‘Shreyanshi’, ‘Kanpur’);

Insert into USER_INFO values(104, ‘Sumit Pal’, ‘Pune’);

Insert into USER_INFO values(105, ‘Ayush Agarwal’, ‘Lucknow’);

Insert into USER_INFO values(106, ‘Aman Kumar’, ‘Delhi’);

Insert into USER_INFO values(107, ‘Divyansh’, ‘Delhi’);

SELECT * From USER_INFO;

The output table “USER_INFO” for the above query will be:

UserID	UserName	Location
101	Vivek Goswami	Noida
102	Neha Singh	Pune
103	Shreyanshi	Kanpur
104	Sumit Pal	Pune
105	Ayush Agarwal	Lucknow
106	Aman Kumar	Delhi
107	Divyansh	Delhi

The syntax for creating a Non clustered index is similar to creating the clustered index, except we don't have to drop any previous clustered index here. It is as follows:

Create NonClustered index IX_table_name_column_name on table_name (column_name ASC);

Let us say we use the following query:

Create NonClustered index IX_USER_INFO_UserName on USER_INFO (UserName ASC);

The output of the above statement will be:

UserName	Row_Address
Aman Kumar	6
Ayush Agarwal	5
Divyansh	7
Neha Singh	2
Shreyanshi	3
Sumit Pal	4
Vivek Goswami	1

Row_Address is the indexing of the rows according to their insertion.

You can also read about the Multiple Granularity Locking,Multiple Granularity in DBMS

FAQs

What are the advantages and disadvantages of the Clustered index?
Advantages:
These indexes are ideal for range with maximum, minimum, or count type queries.
For searching, we can go straight to a specific point in data to keep reading sequentially from there.
It helps to minimize the page transfers and also maximize the cache hits.
Disadvantages:
Lots of insert operations are there in non-sequential order.
There will be extra work for insert, update, and delete operations in SQL.
What are the advantages and disadvantages of the Non-clustered index?
Advantages of Non clustered index:
It helps to retrieve the data from the database table quickly.
Using this, we can avoid the overhead cost associated with the clustered index.
Since we can create one more Non clustered index in a table. It can be used to create more than one index.
Disadvantages of using Non clustered index:
We can not sort the data rows physically in a non clustered index.
The lookup process on Non clustered index is costly.
What are the key differences between clustered and non clustered indexes?
The main differences between them are as follows:
Cluster index sorts the data rows in the table according to their key values, while Non clustered index stores the data at one location and indices at another location.
Cluster index doesn't need extra disk space, whereas Non clustered index needs extra disk space.

Key Takeaways

In this blog, we learn about indexes, then we learn about types of indexes. Later we learn about the commonly used indexes: clustered and non clustered indexes. We learn the syntax of creating these indexes with their suitable examples.