Clustered vs Non-Clustered Index

Every serious SQL Server table has to answer one question first: in what order are the rows physically stored? The clustered index answers that. Every other "shortcut" you add afterwards — on name, team, match_date — is a non-clustered index. Knowing the difference is one of the most common SQL interview questions.

Definition

A clustered index sorts and stores the table's rows physically in the order of the index key. Its leaf level is the data itself, so a table can have only one clustered index.

A non-clustered index is a separate sorted structure that holds the key values plus a pointer (row locator) to the real row. The table's data is not reordered, so a table can have many non-clustered indexes.

Unpacking the words:

• Physically sorted — rows for id 1, 2, 3 … really sit next to each other on the data pages. That is why only one order (one clustered index) is possible — just like a book can be printed in only one page order.
• Leaf level is the data — when the B-Tree search finishes, you are already standing on the row. No extra hop.
• Pointer / row locator — in a non-clustered index the leaf holds the key and a locator. On a heap the locator is a RID (file:page:slot, e.g. 3:3); on a clustered table it is the clustered key value. Following that pointer is called a lookup (RID Lookup or Key Lookup).

Clustered index — search path

Table players with a clustered index on id, finding WHERE id = 11:

3 pages read instead of thousands. Rows are stored sorted by id, so range queries such as WHERE id BETWEEN 5 AND 12 are also fast — SQL finds 5 and reads forward.

Non-clustered index — search path

A non-clustered index on name is a separate list sorted A→Z, each entry with a pointer:

For WHERE name = 'Hardik Pandya', SQL seeks the name index, finds pointer 3:3, then jumps to data page 3, slot 3 to fetch the full row. That extra jump is why a non-clustered index is slightly slower to read than a clustered one.

Clustered vs Non-Clustered — side by side

Syntax

CREATE [CLUSTERED | NONCLUSTERED] INDEX index_name
ON table_name (column1 [ASC | DESC], column2 [ASC | DESC], ...);

-- Clustered: rows physically sorted by id
CREATE CLUSTERED INDEX IX_players_id ON players (id);

-- Non-clustered: a separate index on team
CREATE NONCLUSTERED INDEX IX_players_team ON players (team);

-- Composite + sort order (NONCLUSTERED by default)
CREATE INDEX IX_players_country_runs ON players (country ASC, runs DESC);

Note: In SQL Server, declaring a PRIMARY KEY automatically creates a unique clustered index on it (unless a clustered index already exists). That's why most tables are clustered on their id.

Composite index and the leftmost-prefix rule

A composite index is an index on two or more columns. Its entries are sorted by the first column, then by the second inside each first-column value, and so on.

IX_players_country_runs (country, runs DESC) is like a phone book sorted by country, then by runs. So it helps:

Example — composite index also removes a sort (playground)

CREATE INDEX IX_players_country_runs ON players (country ASC, runs DESC);

SELECT name, runs
FROM players
WHERE country = 'India'
ORDER BY runs DESC;

Without the index, EXPLAIN QUERY PLAN shows SCAN players plus USE TEMP B-TREE FOR ORDER BY (an extra sort step). With it, the plan is just SEARCH players USING INDEX IX_players_country_runs (country=?) — the rows already come out in runs DESC order, so no sort is needed.

Common mistakes

• ❌ Trying to create a second clustered index. ✅ Only one per table — make the others non-clustered.
• ❌ Clustering on a wide, random, frequently-updated column (e.g. a GUID or name). ✅ Cluster on a narrow, unique, static, ever-increasing key such as INT IDENTITY — every non-clustered index copies the clustered key, so a wide key bloats them all.
• ❌ Putting the wrong column first in a composite index. ✅ Put the column you filter with = most often first.

SQL Server vs MySQL / PostgreSQL

• SQL Server: you choose CLUSTERED / NONCLUSTERED explicitly.
• MySQL (InnoDB): the table is always clustered on the primary key; every secondary index is non-clustered. No keyword needed.
• PostgreSQL: tables are heaps; all indexes are effectively non-clustered (CLUSTER re-sorts once but is not maintained).
• SQLite (playground): CLUSTERED / NONCLUSTERED keywords are not supported — write CREATE INDEX.

Interview tip

Q: Why can a table have only one clustered index but many non-clustered ones? A: A clustered index is the physical order of the rows, and data can be stored in only one order. A non-clustered index is just a separate sorted copy of some columns with pointers back to the rows, so you can create many of them.