Clustered vs Nonclustered Indexes in SQL Server: The B-Tree Explained

Every index recommendation you’ve absorbed passively up to this point (“add an index here”) gets its real mechanical foundation in this chapter. Every SQL Server index is a B-tree: a root page, branch pages, and leaf pages. The difference between clustered and nonclustered is what lives at the leaf level — and that single difference explains almost everything else in this chapter.

The B-Tree, Visualized

A seek walks root → branch → leaf, typically just 3-4 page reads even against a table with millions of rows — this is the entire reason indexes matter: it turns “read every row” into “read a handful of pages,” a logarithmic rather than linear cost as the table grows.

Clustered vs Nonclustered

This is worth internalizing precisely: a clustered index doesn’t sit “alongside” the table — for a clustered table, the table is the index. There’s no separate copy of the data; the rows are physically stored in clustered-key order. A nonclustered index, by contrast, is a genuinely separate structure, small and narrow, that only stores its key columns plus a pointer back.

The Key Lookup Problem

A query that filters on a nonclustered index’s column but selects other columns not in that index requires a key lookup — jumping from the nonclustered leaf back to the clustered index to fetch the rest. For a handful of rows this is cheap; for a large result set, SQL Server often abandons the index entirely and scans the whole table, because thousands of individual lookups cost more than one sequential scan.

-- Confirm this tipping-point behavior yourself
CREATE NONCLUSTERED INDEX IX_OrderLog_Status ON dbo.OrderLog (order_status);

-- Selective (few matching rows): optimizer uses the index + key lookups
SELECT * FROM dbo.OrderLog WHERE order_status = 'cancelled'; -- rare status, few rows

-- Unselective (most rows match): optimizer likely abandons the index for a scan
SELECT * FROM dbo.OrderLog WHERE order_status = 'completed'; -- common status, most rows
-- Compare the two actual execution plans (Ctrl+M) to see the optimizer's choice change