Difference Between Clustered and Non-clustered index

In this post, we will understand the difference between clustered index and non-clustered index.

Clustered index

• It is quick.

• It requires less memory to perform operations.

• The index is the main data.

• A table can have one clustered index only.

• It has inherent ability to store data on the disk.

• It can store pointers to block not to data.

• The leaf nodes contain actual data.

• The clustered key defines the order of data within table.

• It is a type of index where the table records are physically reordered to match with the index.

Non-clustered index

• It is slower.

• It requires more memory to perform operations.

• The index is a copy of data.

• A table can have multiple non-clustered indices.

• It doesn’t have the inherent ability to store data on the disk.

• It can store values as well as pointers to actual rows that contain data.

• The leaf nodes are not actual data, they just contain included columns.

• The index key is used to define the order of data within index.

• It is a special type of index wherein the logical order of index doesn’t match with the physical stored order of the rows on the disk.