Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Difference between clustered index and non-clustered index in SQL server
An Index is a lookup table associated with an actual table or View that is used by the database to improve the data retrieval performance timing. In an index, keys are stored in a structure (B-tree) that enables SQL Server to find the rows associated with the key values quickly and efficiently. Index gets created automatically if the primary key and unique key constraints are defined on the table. There are two types of index namely, Clustered Index and Non-Clustered Index.
Read this article to learn more about clustered index and non-clustered index and how they are different from each other.
What is a Clustered Index?
A Clustered index is a type of index where the table records are physically reordered to match with the index. If a Table is created with primary key constraints, then the database engine automatically creates a clustered index. In this, data is sorted or stored in the table or view based on their key and values.
A clustered index contains the main data. It is quick, thus the data retrieval is faster in the clustered index, and it also requires less memory space to perform operations. A clustered index has the inherent ability to store data on the disc.
What is Non-Clustered Index?
A Non-clustered index is a special type of index in which the logical order of the index does not match with the physically stored order of the rows on the disk. In this type of index, the table is created with UNIQUE constraints, then the database engine automatically creates a non-clustered index.
A non-clustered index contains the non-clustered index keyvalues and each keyvalue entry has a pointer to the data row that contains the key value. However, a non-clustered index is relatively slower, and it requires more memory space to perform the operations. Also, a non-clustered index does not have the inherent property of storing data on the disc.
Difference between Clustered Index and Non-Clustered Index
The following table highlights all the major differences between clustered index and non-clustered index in SQL server −
Key |
Clustered Index |
Non-Clustered Index |
|---|---|---|
Basic |
Clustered index is created on primary key. |
Non-clustered index can be created on any key. |
Ordering |
Store data physically according to the order. |
It doesn't impact the order. |
Number of index |
Only one clustered index can be there in a table. |
There can be any number of non-clustered indexes in a table. |
Space |
No extra space is required to store logical structure. |
Extra space is required to store logical structure. |
Performance |
Data retrieval is faster than non-cluster index. |
Data update is faster than clustered index. |
Leaf node |
The leaf nodes contains the actual data in the clustered index. |
The leaf nodes does not contain the actual data in the non-clustered index. |
Number of indices |
A table can have only one clustered index. |
A table can have multiple non clustered indices. |
Data |
Clustered index contains main data. |
Non-clustered index contains a copy of data. |
Conclusion
The most significant difference that you should note here is that a clustered index can be created on the primary key only, while a non-clustered index can be created on any key.
7K+ Views
