Open In App

SQL Queries on Clustered and Non-Clustered Indexes

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Share
Report issue
Report

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, the 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.

  • Clustered index
  • Non-clustered index

Clustered Index

A 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 the clustered index which is self-created on that primary key will sort the Student_info table as per ROLL_NO. A clustered index is like a Dictionary; in the dictionary, the sorting order is alphabetical and there is no separate index page. 

Examples:

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.

Non-Clustered Index

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:

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 offers faster data access, on the other hand, the Non-clustered index is slower.

Clustered and non-clustered indexes in SQL Server can provide significant performance benefits when querying large tables. Here are some examples of SQL queries and the advantages of using clustered and non-clustered indexes:

SELECT Queries with WHERE Clause

  • Clustered Index: When a SELECT query with a WHERE clause is executed on a table with a clustered index, SQL Server can use the clustered index to quickly locate the rows that match the WHERE condition. This can be very efficient for large tables, as it allows the database engine to minimize the number of disk reads required to retrieve the desired data.
  • Non-Clustered Index: If there is no clustered index on the table or the WHERE clause references columns that are not part of the clustered index, SQL Server can use a non-clustered index to find the matching rows. However, this may require additional disk reads if the non-clustered index does not include all the columns required by the query.

UPDATE Queries

  • Clustered Index: When an UPDATE query is executed on a table with a clustered index, SQL Server can use the index to quickly locate and modify the rows that match the query criteria. This can be very efficient for large tables, as it allows the database engine to minimize the number of disk writes required to modify the data.
  • Non-Clustered Index: If the UPDATE query references columns that are not part of the clustered index, SQL Server may need to perform additional disk writes to update the non-clustered index as well.

JOIN Queries

  • Clustered Index: When performing a JOIN operation between two large tables, SQL Server can use the clustered index on the join column(s) to efficiently match the rows from both tables. This can significantly reduce the time required to complete the query.
  • Non-Clustered Index: If the JOIN operation references columns that are not part of the clustered index, SQL Server can use a non-clustered index to find the matching rows. However, this may require additional disk reads and slow down the query.
  • In general, the advantage of using clustered indexes is that they can provide very efficient access to large tables, particularly when querying on the index columns. The advantage of using non-clustered indexes is that they can provide efficient access to columns that are not part of the clustered index, or when querying multiple tables with JOIN operations. However, non-clustered indexes can also require additional disk reads or writes, which can slow down queries. It is important to carefully design and tune indexes based on the specific query patterns and data access patterns of your application.

Conclusion

  • When read efficiency is important and the indexed column has a natural order, use a clustered index.
  • When optimizing particular query patterns or when there are frequent alterations to the data, choose a non-clustered index.
  • To make an informed choice on index types, take into account the entire workload and access patterns.
  • To adjust to evolving application needs, track and assess index performance on a regular basis.


Last Updated : 08 Mar, 2024
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads