Difference Between Clustered and Non-clustered index

DatabaseData StorageSoftware & Coding

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.

raja
Published on 25-Mar-2021 05:48:22
Advertisements