SQL queries on clustered and non-clustered Indexes

Prerequisite – Indexing in Databases
Indexing is a procedure that returns your requested data faster from the defined table. Without indexing, the SQL server has to scan the whole table for your data. By indexing, SQL server do the exact same thing when you searched for a content in a book by checking the index page. In the same way table’s index allows us to locate the exact data without scanning the whole table. There are two types of indexing in SQL.

Clustered index
Non-clustered index

1. Clustered –
Clustered index is the type of indexing that established a physical sorting order of rows.Suppose you have a table Student_info which contains ROLL_NO as a primary key than Clustered index which is self created on that primary key will sort the Student_info table as per ROLL_NO. Clustered index is like Dictionary, in the dictionary sorting order is alphabetical there is no separate index page.

Examples:

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:

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

If we want to create Clustered index on other column then first we have to remove the primary key after that we can remove the previous index.

Note that defining a column as a primary key makes that column the Clustered Index of that table. To make any other column, clustered index first we have to remove the previous one as follows below procedure.

Syntax:

//Drop index
drop index table_name.index_name
//Create Clustered index index
create Clustered index IX_table_name_column_name 
             on table_name (column_name ASC)

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

2. Non-clustered:
The Non-Clustered index is an index structure separate from the data stored in a table that reorders one or more selected columns. The non-clustered index is created to improve the performance of frequently used queries not covered by clustered index. It’s like a textbook, the index page is created separately at the beginning of that book.

Examples:

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:

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

Note: We can create one or more Non_Clustered index in a table.

Syntax:

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

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

Clustered vs Non-Clustered index:

In a table there can be only one clustered index or one or more tha one non_clustered index.
In Clustered index there is no separate index storage but in Non_Clustered index there is separate index storage for the index.
Clustered index is slower than Non_Clustered index.

Don’t stop now and take your learning to the next level. Learn all the important concepts of Data Structures and Algorithms with the help of the most trusted course: DSA Self Paced. Become industry ready at a student-friendly price.

My Personal Notes

Recommended Posts: