Clustered and Non-clustered index are the types of single-level ordering index where clustered index determines how the data is stored in the rows of a table. On the other hand, the non-clustered index stores the data at a single place and the indexes are stored at another place. Additionally, each table can have only one clustered index. As against, In case of a non-clustered index, a table can have multiple non-clustered indices.
The indices are essentially required for the efficient enforcement of integrity constraints and efficient processing of queries and transactions. These are created on tables and views. For example, the indices used in books which facilitates a user to quickly access the content of a book, similarly we have the indices on the SQL.
Content: Clustered Index Vs Non-clustered Index
Comparison Chart
| Basis for comparison | Clustered Index | Non-clustered index |
|---|---|---|
| Basic | Determines the storage order of the rows in a table as a whole. | Determines the storage order of the rows in a table with the help of separate physical structure. |
| Number of indexes allowed per table | Only one clustered index | Multiple non-clustered indices |
| Data accessing | Faster | Slower as compared to the clustered index |
| Additional disk space | Not needed | Required to store the indices separately |
Definition of Clustered index
The Clustered index is basically used to order the rows in a table. A table can have only one clustered index because the rows in a table can be sorted in only one order, but there are ways to create a composite clustered index. The columns are included in the clustered index and the logical or indexed order of the key values is the same as the physically stored order of the corresponding rows. When there is no clustered index for the data, it is stored in a heap.
The record accessing in a heap is very time consuming, where each entry in a table is scanned to access the desired data. In the table scan, there was no way to find out whether there are more matches available or not. So, this method was very inefficient.
While using a clustered index, the data accessing is faster and systematic where the table is organised in some order. It is defined in the ordering field of the table. Its search key is used specifies the sequential arrangement of the file or table. A clustered index is automatically created when a primary key is defined for a table.
Definition of Non-clustered index
The Non-clustered index stores the data at one place and indices at a different place and the index would have pointers to the storage location of the data. A table can have multiple non-clustered indices because the index in the non-clustered index is stored at a different place. For example, a book can have more than one index, one at the beginning which shows the contents of a book unit wise and another index at the end which shows the index of terms in alphabetical order.
It is defined in the non-ordering field of the table. The non-clustered index could enhance the performance of queries that use keys other than primary keys. A non-clustered index is automatically created when a unique key is defined for a table.
Key Differences Between Clustered and Non-clustered index
- The number of clustered index that a table can have is only one. Whereas a table can have multiple non-clustered indices.
- A clustered index is faster than non-clustered index because the non-clustered index has to refer back to the base table. On the contrary, this is not the case in the clustered index.
- In a non-clustered index, the index is stored in a separate location which requires additional storage space. In contrast, clustered index stores the base table data in same physical order as index’s logical order, so it does not require additional storage space.
Conclusion
The clustered index is a way of storing data in the rows of a table in some particular order. So that when the desired data is searched, the only corresponding row gets affected that contain the data and is represented as output. On the other hand, the non-clustered index resides in a physically separate structure that references the base data when it is searched. A non-clustered structure can have a different sort order.
Leave a Reply