Clustered vs. Non-clustered indexes

The difference between a clustered and a non-clustered index lies in how they store data and their impact on data retrieval:

• Clustered Index:
• A clustered index sorts and stores the data rows in the table based on their key values.
• There can be only one clustered index per table because it defines the physical order of data storage.
• The leaf nodes of a clustered index contain the actual data pages.
• It does not require additional disk space beyond the space used by the data itself.
• Offers faster data access due to the physical ordering of records.
• Non-Clustered Index:
• A non-clustered index stores the index entries in one location and the actual data in another.
• A table can have multiple non-clustered indexes.
• The leaf nodes of a non-clustered index contain pointers to the actual data.
• Requires additional disk space for storing the index structures separately from the data.
• Generally slower than clustered indexes for data retrieval because it requires an extra step of following pointers to retrieve the actual data.

In summary, a clustered index affects the physical storage of data and is directly tied to the data itself, while a non-clustered index is a separate structure that points to the data¹². Both types of indexes are used to speed up the retrieval of data but do so in different ways.

(1) Difference between Clustered and Non-clustered index – Guru99. https://www.guru99.com/clustered-vs-non-clustered-index.html.
(2) Clustered and nonclustered indexes – SQL Server | Microsoft Learn. https://learn.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described?view=sql-server-ver16.
(3) Difference Between Clustered and Non-Clustered Index. https://www.geeksforgeeks.org/difference-between-clustered-and-non-clustered-index/.