Clustered vs Non-Clustered Indexes: Quick Performance Guide

An index is crucial for search speed. However, clustered and non-clustered variants behave differently:

• Clustered Index: Sorts and stores the actual physical data rows of the table based on the key value. Because data rows can only be sorted in one order, a table can have only one clustered index (usually the Primary Key). • Non-Clustered Index: Houses index keys alongside pointers to the physical data rows (row IDs or clustered index keys). A table can have multiple non-clustered indexes.

Analogy: A clustered index is like a dictionary (words are physically sorted in alphabetical order). A non-clustered index is like the index at the back of a textbook (words point to page numbers where the actual content resides).