HuntExams Academy logo
SQLAdvanced#indexes

Clustered vs non-clustered index?

Answer

A clustered index defines the physical storage order of the rows: the table's data pages ARE the leaf level of that index. A non-clustered (secondary) index is a separate structure holding the key plus a pointer \u2014 a row locator, or the clustered key \u2014 used to fetch the rest of the row. Clustered index: - Only one per table, because rows can be physically sorted just one way. - In SQL Server the PRIMARY KEY becomes the clustered index by default. In MySQL/InnoDB every table has one: the PK, else a unique NOT NULL key, else a hidden row id. - Excellent for range scans and ORDER BY on the key, because matching rows sit next to each other \u2014 sequential I/O. - No extra lookup: reaching the leaf means you already have the whole row. - A wide or random key (for example a random UUID) hurts badly \u2014 it bloats every secondary index and causes page splits. Prefer a narrow, increasing key. Non-clustered index: - Many per table, limited only by write cost. - The leaf stores key + locator, so the engine usually needs a second read for remaining columns \u2014 a key lookup / bookmark lookup. When that cost is high across many rows the optimiser abandons the index and scans instead (the tipping point). - INCLUDE columns in SQL Server, or a wider composite key, turn it into a covering index and remove the lookup entirely. Interview one-liner: clustered = the data itself, kept sorted; non-clustered = a sorted pointer list you follow back to the data. One clustered, many non-clustered.

Example

-- SQL Server
CREATE TABLE orders (
  order_id    INT IDENTITY PRIMARY KEY,     -- clustered by default
  customer_id INT  NOT NULL,
  order_date  DATE NOT NULL,
  total       DECIMAL(10,2)
);

-- Non-clustered: needs a key lookup to read total
CREATE NONCLUSTERED INDEX idx_orders_customer ON orders(customer_id);

-- Covering version: no key lookup
CREATE NONCLUSTERED INDEX idx_orders_customer_cover
  ON orders(customer_id) INCLUDE (order_date, total);

-- Clustered range scan is sequential
SELECT * FROM orders WHERE order_id BETWEEN 1000 AND 2000;

-- MySQL/InnoDB: secondary index leaves store the primary key,
-- so a wide PK makes every secondary index bigger.

Detailed explanation

A clustered index stores the table’s rows at the leaf level of its B+ tree; a non-clustered index is a separate B+ tree containing indexed keys and row locators. Both use upper levels to navigate to matching leaf entries. Clustering determines the logical ordering of data pages by key, not guaranteed contiguous placement on disk. A table can have only one clustered index because its rows have one leaf-level organisation, but it can have multiple non-clustered indexes, subject to engine limits and maintenance costs.

The details depend on the database engine. In SQL Server, a PRIMARY KEY normally creates a clustered index if no clustered index already exists and NONCLUSTERED is not specified. A table without a clustered index is a heap. Non-clustered index locators are clustered keys for clustered tables and row identifiers for heaps. In MySQL/InnoDB, every table has a clustered index: the primary key, otherwise the first suitable UNIQUE index whose columns are all NOT NULL, otherwise a generated hidden row ID. InnoDB secondary indexes contain the clustered key as the row locator.

Clustering is useful for range predicates because nearby key values occupy logically neighbouring leaf pages, supporting efficient traversal and read-ahead. Reaching a clustered leaf gives access to the base row without a separate index-to-table lookup, although off-row values may require additional reads. A non-clustered index can find qualifying entries quickly, but retrieving missing columns requires key lookups in SQL Server or clustered-index lookups in InnoDB. When many rows qualify, repeated lookups may cost more than scanning, so the optimiser can choose a scan instead.

For example, with orders clustered on order_id, a non-clustered index on customer_id helps locate one customer’s orders. In SQL Server, adding INCLUDE (order_date, total) covers a query selecting those columns and avoids base-row lookups. Index design also affects writes: every relevant index must be maintained. Wide clustered keys enlarge secondary row locators, while random insertion patterns can increase page splits and fragmentation. Narrow, stable, increasing keys are often useful, but increasing keys can create hot-page contention. Interviewers expect these trade-offs and the reminder that only ORDER BY guarantees result ordering.

Key points to remember

  • Clustered leaves hold base rows; non-clustered leaves hold index entries and row locators.
  • A table has at most one clustered index but can have multiple non-clustered indexes.
  • SQL Server supports heaps; InnoDB always organises table data through a clustered index.
  • Covering indexes avoid base-row lookups by supplying every column needed by a query.
  • Choose keys using read patterns, key width, insertion behaviour, and write-maintenance costs.

Common mistakes candidates make

  • Claiming clustered pages are necessarily contiguous on disk or that queries return sorted results without ORDER BY.
  • Assuming a primary key is always clustered across all database engines and configurations.
  • Saying every non-clustered access requires exactly one extra physical read, ignoring covering indexes, caching, and tree traversal.

Likely follow-up questions

How does a covering index differ from a composite index?

A composite index has multiple key columns, which participate in its ordering. Covering describes whether an index supplies everything a particular query needs; SQL Server INCLUDE columns can provide coverage without becoming search-key columns.

Why might the optimiser ignore a non-clustered index?

If many rows qualify, traversing the index and performing repeated base-row lookups may cost more than scanning. The choice depends on estimated selectivity, statistics, coverage, and the engine’s cost model, not a universal percentage threshold.

Should the primary key always be the clustered key?

Not necessarily: a primary key enforces row identity, while clustering is a storage-design decision. SQL Server lets you choose different keys for these roles; InnoDB uses the declared primary key for clustering.

Interview tip: Start with what each index stores at its leaves, then explain one lookup example and qualify the behaviour by database engine.

Related Interview Questions

1
SQLAdvanced#indexes#performance

What is a covering index?

Open
2
SQLAdvanced#indexes#performance

Why might an index not be used by a query?

Open
3
SQLIntermediate#subqueries

What is a subquery? Correlated vs non-correlated?

Open
More SQL interview questions →