What is the Difference between Clustered and Non-clustered Index?
FREE
Nowadays, we use databases to store records, and to fetch records much more efficiently, we use indexes. The index is a unique key made up of one or more columns. There are two types of indexes:
- Clustered Index
- Non-Clustered Index
Clustered Index
A clustered index is an index that specifies the physical arrangement of a database's table records. There can only be one clustered index per table since there can only be one method that records are physically maintained in a database table. It stores the records in sorted order.
Non-Clustered Index
A non-clustered index is an index that doesn't specify the physical arrangement of the records maintained in the database’s table. The Non-Clustered Indexes are stored in a different table. Therefore, as they are maintained in a different table, there can be numerous non-clustered indexes that can be created for a single table.
Transform Your Career
Choose from our industry-leading programs designed for career success
Modern Software and AI Engineering Program
Master full-stack development with AI integration
+1000 moreModern Data Science and ML with specialisation in AI
Advanced data science techniques with AI specialization
+1000 moreAdvanced AIML with Specialisation in Agentic AI
Deep dive into AIML with focus on Agentic systems
+1000 moreDevOps, Cloud & AI Platform Engineering
Build and manage AI-powered cloud infrastructure
+1000 moreAI Engineering Advanced Certification by IIT-Roorkee
Premier AI engineering certification from IIT-Roorkee
Characteristics of the Clustered Index
The characteristics of a clustered index are as follows:
- Default Indexing Methodology
- can use a single or more than one column for indexing.
- Indexes are stored in the same table as actual records.
- It supports fragmentation functionality.
- Supports Scanning and Seeking using Clustered Indexes.
- It supports key lookup functionality.
Characteristics of the Non-Clustered Index
The characteristics of the non-clustered index are as follows:
- Table data is stored in the form of key-value pairs.
- Tables or Views can have indexes created for them.
- It contains pointers to clustered index records.
- Supports Index Scanning and Seeking Functionalities.
- It provides secondary access to records.
- A non-clustered key-value pair and a row identifier are stored in each index row of the non-clustered index.
Examples of Clustered and Non-Clustered Index
Clustered Index:
For Example, While Fetching records of an employee in an organization, we can directly access the records of the employee using his unique employee Id.
Query:
Non-clustered Index:
For example, while fetching the records of a product in a city, we can create an index using pincode and product id to fetch the records of each product efficiently and faster.
Index Creation Query:
Query:
Differences between Clustered and Non-clustered Index
| Clustered Index | Non-Clustered Index |
|---|---|
| The Clustered Index focuses on physical structure. | The non-clustered index focuses on logical structure. |
| The clustered index is more efficient, i.e., faster. | The Non-Clustered index is less, i.e., slower. |
| In a clustered index, the index is stored with the main data. | In a non-clustered index, the index is stored in a different table. |
| There can only be one clustered index. | There can be several non-clustered indexes. |
| The Index key represents the records in the database table. | The Index Key represents the order of records within the index of the database table. |
Creating Clustered index
Clustered indexes are automatically created by the database for any table. Therefore, to create a new clustered index, we first need to delete the old clustered index. After deleting the index, we use the syntax given below to create a new clustered index.
Syntax:
Example:
Explanation: The above query will create a clustered index on the Emp table using the column name, which will make the records inserted in ascending order of the name by default.
Scaler Placement Report and Statistics
Scaler learners achieved 2.5x salary growth with average post-Scaler CTC reaching ₹23L.
Google
Amazon
Microsoft
Flipkart
Adobe1200+ moreCreating a Non-Clustered Index
As Non-Clustered Indexes are maintained in different tables, they can be numerous. So we don't need to delete previously created indexes. The syntax for creating a Non-Clustered Index is similar to the syntax for a clustered index except for the use of non-clustered Words. We can create a non-clustered index using the syntax given below:
Syntax:
Example:
Explanation: The above query will create a NONCLUSTERED Index on the Emp table using the column name, which will make the records inserted in ascending order of the name.
When to Use a Clustered or Non-Clustered Index?
- A Clustered Index should be used when we need to specify the physical order of the records inserted into a table. It is more efficient in time, i.e., faster than a non-clustered index.
- A Non-Clustered Index should be used when we need to logically maintain a sequence of data without altering the physical sequence or several indexes are needed to maintain the table. It is less efficient in time, i.e., slower than a clustered index, and also stores indexes in a different table.
Advantages of Clustered and Non-Clustered Index
Turn Learning into Career Growth
Clustered Index
- For range or group queries that are generally used with max, min, or count functions, clustered indexes are the best solution.
- In a clustered index, a pointer can directly go to a certain place in the data so that you can continue reading in order from there.
- The Clustered Index helps you increase cache visits and decrease page transfers.
- Clustered indexes help in locating the index entry at the beginning of a range using a location mechanism.
Non-Clustered Index
- Non-Clustered Indexing aids you in avoiding the administrative costs related to clustered indexes
- In RDBMS, a table may contain many non-clustered indexes. Therefore, numerous indexes may be created using it.
- You may easily access information from the table with the use of a non-clustering index.
Disadvantages of Clustered and Non-Clustered Index
Clustered Index
- Numerous constant page splits, including data pages and index pages, are produced by clustered indexes.
- In Clustered Indexes, SQL queries like insert, delete, and update take more execution time and system resources.
- Most of the records in the clustered index are located in the leaf nodes.
Non-Clustered Index
- A non-clustered index allows you to store data in a logical order. A non-clustered index does not allow you to physically sort data records.
- The cost of a lookup on a non-clustered index increases.
- The non-clustered index must also be updated if the clustering key is altered because it includes the clustering key.
Conclusion
In this article, we learned about clustered and non-clustered indexes. Some important points are as follows:
- The index is a unique key made out of one or more than one column.
- The Clustered Index focuses on Physical structure.
- The Non-Clustered Index focuses on Logical Structure.
- Clustered indexes contain data, whereas non-clustered indexes are stored in a different table.
- Clustered indexes are faster than non-clustered indexes.
See Also
To learn more about indexes in the database, please go through the below link.