What is the difference between clustered and non-clustered indexing?
Interview preparation resource from Gate Smashers.
A clustered index determines the table's physical row order according to the index key, while a non-clustered index is a separate ordered structure that contains keys and references (row locators) to the actual table rows. A table usually has only one clustered index but can have many non-clustered indexes; the clustered index’s leaf level contains the actual data rows, whereas a non-clustered index’s leaf level contains row locators or key references.
Core difference
A clustered index determines the order or organization of table rows according to the index key, i.e., it is tied to the table's main physical or clustered storage order. A non-clustered index is a separate structure that contains keys and references that lead to the actual table rows and does not reorganize the table in the same way.
Key properties (concise)
- Data organization: Clustered: Determines table's row/order organization. Non-clustered: Separate index structure.
- Number per table: Clustered: Usually one. Non-clustered: Can be many.
- Leaf level: Clustered: Contains/represents the actual data rows or clustered entries. Non-clustered: Contains row locator / key reference.
- Range scans: Clustered: Often very efficient when range follows clustered key. Non-clustered: Can be efficient but may require additional row lookups.
- Use case: Clustered: Range access and ordered retrieval. Non-clustered: Alternate lookup paths.
Summary and practical note
Because table rows can only have one main physical organization at a time, a table usually has only one clustered index (or clustered storage order) but can have multiple non-clustered indexes to support different search patterns. This allows efficient ordered retrieval when the access pattern matches the clustered key and alternate lookup paths via non-clustered indexes when it does not.
Implementation caveat
Exact storage behavior varies between DBMS products, but the important concept is that a clustered index is tied to the table's main row organization, while a non-clustered index is an additional lookup structure.