Clustered and nonclustered indexes

SQL Server stores indexes as B-trees: a root page, intermediate pages, and a leaf level of pages in key order. The two kinds of index differ in what the leaf level holds.

Clustered: the index is the table

A clustered index's leaf level is the table's data: every column of every row, kept in clustered-key order. That is why a table has at most one. A table without one is a heap, whose rows sit in no particular order and are found by a physical row id (RID).

Declaring a PRIMARY KEY creates a clustered index by default, unless the table already has one or you write PRIMARY KEY NONCLUSTERED. The primary key and the clustered index are separate decisions that merely default to the same columns.

1CREATE TABLE orders (id INT CONSTRAINT pk_orders PRIMARY KEY, customer_id INT NOT NULL, total INT NOT NULL);2CREATE INDEX ix_orders_customer ON orders (customer_id) INCLUDE (total);3CREATE TABLE events (id INT NOT NULL, happened_at DATETIME2 NOT NULL);4CREATE TABLE audit (id INT CONSTRAINT pk_audit PRIMARY KEY NONCLUSTERED, logged_at DATETIME2 NOT NULL);5CREATE CLUSTERED INDEX cx_audit_logged ON audit (logged_at);

1SELECT OBJECT_NAME(i.object_id) AS table_name,2       COALESCE(i.name, N'(none)') AS index_name,3       i.type_desc,4       i.is_primary_key5FROM sys.indexes AS i6WHERE i.object_id IN (OBJECT_ID('orders'), OBJECT_ID('events'), OBJECT_ID('audit'))7ORDER BY table_name, i.index_id;

Nonclustered: key plus a pointer

A nonclustered index is a second B-tree. Its leaf rows hold the index key columns plus a row locator: the clustered key if the table has a clustered index, or the RID if it is a heap. A query that needs only those columns is answered from the index alone. A query that needs more must go from each matching index row to the base table to fetch the rest. In a plan that step is a Key Lookup (or RID Lookup on a heap), executed once per row.

INCLUDE adds columns to the leaf level without making them part of the key, so the index covers the query:

CREATE INDEX ix_orders_customer
ON orders (customer_id)
INCLUDE (placed_on, total);

Because every nonclustered index carries the clustered key, a wide clustered key makes every index wider. The usual advice for the clustered key: narrow, unique, unchanging, and increasing. If it is not unique, SQL Server adds a hidden 4-byte uniqueifier to duplicate keys.

Composite keys: only a prefix can be sought

An index on (customer_id, placed_on) is sorted by customer_id, and by placed_on only within each customer. A seek needs a starting point in that order, so it can use customer_id, or customer_id and placed_on, but not placed_on alone: those rows are spread across the whole index. SQL Server has no skip-scan that would work around this.

The example uses the table hints FORCESEEK, INDEX(ix_cust_placed) as a probe: they demand a seek on that index. If no seek is possible, SQL Server refuses to compile the query with error 8622. A probe that compiles shows only that a seek is ; it says nothing about which plan the optimizer would pick without the hints.

1CREATE TABLE orders (id INT PRIMARY KEY, customer_id INT NOT NULL, placed_on DATE NOT NULL, total INT NOT NULL);2CREATE INDEX ix_cust_placed ON orders (customer_id, placed_on);3INSERT INTO orders VALUES (1, 7, '2024-01-05', 10), (2, 7, '2024-02-05', 20), (3, 8, '2024-01-05', 30);

1DECLARE @probes TABLE (id INT, predicate NVARCHAR(60), query NVARCHAR(300));2INSERT INTO @probes VALUES3  (1, N'customer_id = 7', N'SELECT COUNT(*) FROM orders WITH (FORCESEEK, INDEX(ix_cust_placed)) WHERE customer_id = 7'),4  (2, N'customer_id = 7 AND placed_on >= 2024-02-01', N'SELECT COUNT(*) FROM orders WITH (FORCESEEK, INDEX(ix_cust_placed)) WHERE customer_id = 7 AND placed_on >= ''20240201'''),5  (3, N'placed_on = 2024-01-05', N'SELECT COUNT(*) FROM orders WITH (FORCESEEK, INDEX(ix_cust_placed)) WHERE placed_on = ''20240105''');6 7DECLARE @log TABLE (id INT, predicate NVARCHAR(60), can_seek NVARCHAR(30));8DECLARE @i INT = 1, @p NVARCHAR(60), @q NVARCHAR(300);9WHILE @i <= 310BEGIN11  SELECT @p = predicate, @q = query FROM @probes WHERE id = @i;12  BEGIN TRY13    EXEC sp_executesql @q;14    INSERT INTO @log VALUES (@i, @p, N'yes');15  END TRY16  BEGIN CATCH17    INSERT INTO @log VALUES (@i, @p, CONCAT(N'no, error ', ERROR_NUMBER()));18  END CATCH;19  SET @i += 1;20END;21 22SELECT predicate, can_seek FROM @log ORDER BY id;

Put the column you filter on with equality first, and the one you filter by range (or sort by) after it.

Common mistakes

• Assuming the primary key must be the clustered index. It is only the default.
• An index on (a, b) for a query filtering on b. The second example shows a seek on that index is impossible; at best the index is scanned.
• A random GUID as the clustered key. New rows land in random places in the B-tree, and the 16-byte key is copied into every nonclustered index.
• Adding every selected column to the key instead of to INCLUDE; key columns are stored and sorted at every level of the B-tree, included columns are stored only at the leaf and are not sorted.

What interviewers ask

• Clustered versus nonclustered? The clustered leaf is the row data in key order, one per table; a nonclustered index holds key columns plus a pointer to the row.
• What is a key lookup and how do you remove it? A per-row trip from a nonclustered index to the clustered index for missing columns; add them with INCLUDE, or select fewer columns.
• Will an index on (last_name, first_name) help WHERE first_name = 'Ana'? Not with a seek; the leading column is not constrained.