In a relational database, the data is stored in tables. These tables are related to each other using constraints such as foreign keys. A database consists of multiple tables. Sometimes it is hard to search the required data. Therefore, the indexes can be used to speed up the searching. The index used in a database is similar to the index of a book. An index of a book contains the chapter with corresponding page numbers. The indexing of the database is similar to that. An index has the structure similar to a table and requires some database space. There are two types of indexes known as Clustered and Non-Clustered Index. In Clustered Index, the logical order of the index matches the physical order of table rows. In Nonclustered Index, the index and actual data are in separate locations so the index is working like a pointer to fetch the real data. The key difference between clustered and nonclustered index is that the clustered index organizes the actual data while the nonclustered index points to the actual data. When there are many indexes and when the data storing is increased, those indexes should also be updated. Therefore, it is important to create indexes according to the application as it can decrease the speed.
CONTENTS
1. Overview and Key Difference
2. What is Clustered Index
3. What is Nonclustered Index
4. Similarities Between Clustered and Nonclustered Index
5. Side by Side Comparison – Clustered vs Nonclustered Index in Tabular Form
6. Summary
What is Clustered Index?
In a clustered index, the index organizes the actual data. It is similar to a phone directory. The phone numbers are organized according to the alphabetical order. The corresponding phone number can be found when searching a particular name. Therefore, the clustering index contains the actual data in an organized way. There can be one index per table.
Figure 01: Cluster vs Nonclustered Index
The primary key is used to specify each entry in the table. In a student table, the student-id can be used as the primary key. In the customer table, the customer_id can be the primary key. Generally, the primary key can be considered to create the clustered index. Basically, in clustered index, the data accessing is systematic and fast because the index logical order and the table order are in the same.
What is Nonclustured Index?
In a nonclustered index, the index is pointing to the actual data. The nonclustered index is the reference to the data. Therefore, there can be multiple indexes per table. An example for a nonclustered index is a book which contains the main index with the caption and the corresponding page number or the index at the end of the book with important terms in alphabetical order with the corresponding page number. This index does not contain the actual data. But it provides the information necessary to reach the actual data. Therefore, the index and the data are in separate locations. So, it required additional storage space.
The non-clustered index is used when there are keys other than the primary key. Generally, the nonclustered index is slower than the clustered index.
What is the Similarity Between Clustered and Nonclustered Index?
- Both Clustered and Nonclustered Index are types of indexes used to search data efficiently.
What is the Difference Between Clustered and Nonclustered Index?
Clustered vs Nonclustered Index |
|
| A clustered index is a type of index where the table records are physically re-ordered to match the index. | A nonclustered index is a type of index that contains the references to the actual data. |
| Number of Indexes | |
| There can be one clustered index per table. | There can be many non-clustered indexes per table. |
| Speed | |
| The clustered index is faster than the Nonclustered Index. | The nonclustered index is slower than the clustered index. |
| Required Space | |
| The clustered index does not require an additional space. | The nonclustered index required an additional space. |
Summary – Clustered vs Nonclustered Index
A relational database contains a lot of data. Therefore, it is important to have a specific mechanism to search data quickly. Indexes can be used to achieve this task. There are two types of indexes. They are clustered and non-clustered index. This article discussed the difference between clustered and non-clustered Index. In clustered index, the logical order of the index matches the physical order of table rows. In nonclustered index, the index and actual data are in separate locations so there are pointers to fetch the data. The difference between clustered and nonclustered index is that the clustered index organizes the actual data while the nonclustered index points to the actual data.
Reference:
1.CalebTheVideoMaker2. Database Design 39 – Indexes (Clustered, Nonclustered, Composite Index), CalebTheVideoMaker2, 17 Jan. 2015. Available here
2.“What is Clustered Index? – Definition from Techopedia.” Techopedia.com. Available here