What is the Difference Between Cluster and Non Cluster Index?

The main difference between clustered and non-clustered indexes lies in how they sort and store data in a table. Here are the key differences between the two:

1. Data sorting and storage: A clustered index sorts the data rows in the table based on their key values, while a non-clustered index stores the data at one location and the indices at another location.
2. Data pages storage: Clustered indexes store data pages in the leaf nodes of the index, while non-clustered indexes never store data pages in the leaf nodes of the index.
3. Memory consumption: Clustered indexes demand less memory to execute operations, while non-clustered indexes demand more memory.
4. Speed: Clustered indexes offer faster data access compared to non-clustered indexes.
5. Number of indexes: A single table can have only one clustered index, but it can have multiple non-clustered indexes.
6. Data storage: Clustered indexes store data on the disk, while non-clustered indexes do not have the inherent ability to store data on the disk.

In summary, clustered indexes are used to define the order or sort the data in a table, storing data pages in the leaf nodes of the index and offering faster data access. On the other hand, non-clustered indexes collect data at one place and store records at another place, demanding more memory and being slower than clustered indexes.

Comparative Table: Cluster vs Non Cluster Index

Here is a table comparing the differences between clustered and non-clustered indexes:

In summary, a clustered index physically reorders table records to match index key values, stores data on the disk, and is faster than non-clustered indexes. On the other hand, non-clustered indexes logically sort the index but not the actual data, do not store data on the disk, and are slower than clustered indexes.