Database Related

Differences Between Non-Clustered And Clustered Indexes

A clustered index helps in determining the order in which the rows of the table will be stored on disk. Beside this, it basically stores row level data in the index’s leaf nodes itself. However, a non-clustered index has no impact on which the order of the rows is going to be stored.

Advantage of Using a Clustered Index

When groups of data that can be clustered are recurrently accessed by some queries, then using a clustered index is an advantage. This is because; it speeds up retrieval as the data lives close to each other on the disk. Moreover, if the data is accessed in the similar order as the clustered index, then the retrieval will be much quicker because the physical data deposited on disk is kept in the same order as the index.

Disadvantage of Using a Clustered Index

The major disadvantage of using a clustered index is that whenever a change is made to a value of an indexed column, the following possibility to re-sort rows for maintaining order is a sure performance hit.

Single Clustered Index on a Table

A clustered index governs the order in which the rows will be deposited on disk, thus having more than one clustered index on one table is not possible. Suppose if we have two clustered indexes on one table – which index would control the order in which the rows will be deposited? Since the rows of a table can just be sorted to follow only one index, having multiple clustered index is not acceptable.

Several Non-Clustered Indexes on a Table

Since there is no impact on the order in which all the rows are deposited on disk, there can be several non-clustered indexes on a table.

Non-Clustered Indexes

Non clustered indexes store both a pointer and a value to the actual row that is holding that value. Clustered indexes don’t require storing a pointer to the actual row. This is because the rows in the table are deposited on disk in the similar order as the clustered index, whereas the clustered index actually deposits the row-level data in its leaf nodes.

Leave a Reply