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 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)
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:
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.
Recommended Posts:
- SQL indexes
- Python | Change column names and row indexes in Pandas DataFrame
- SQL | Top-N Queries
- SQL | SUB Queries
- SQL | Sub queries in From Clause
- CSS | media queries
- DBMS | Nested Queries in SQL
- Mitigation of SQL Injection Attack using Prepared Statements (Parameterized Queries)
- Comparing float value in PHP
- Perl | Modules
- How to remove extension from string in PHP?
- How to make an image center-aligned (vertically & horizontally) inside a bigger div using CSS?
- What does the CSS rule “clear: both” do?
- How to break line without using <br> tag in HTML / CSS ?
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.