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.

  1. Clustered index
  2. Non-clustered index

1. Clustered –
Clustered index is the type of indexing that established a physical shorting 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 short the Student_info table as per ROLL_NO. Clustered index is like Dictionary, in the dictionary shorting 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 bellow 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.


My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.




Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.