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.