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 will do the exact same thing you do when searching for content in a book by checking the index page. In the same way, a table’s index allows us to locate the exact data without scanning the whole table. There are two types of indexing in SQL.

1. Clustered index
2. Non-clustered index

1. Clustered – Clustered index is the type of indexing that establishes a physical sorting order of rows. Suppose you have a table Student_info which contains ROLL_NO as a primary key, then 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 and 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 a Clustered index on another column, first we have to remove the primary key, and then 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, the clustered index, first we have to remove the previous one as follows below. 

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: 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 a 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 than 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.

My Personal Notes arrow_drop_up

Save