SQL Server Index Syntax: Covering Indexes, INCLUDE Columns, and Filtered Indexes

Beyond a basic index, two techniques do most of the real performance work: covering indexes, which eliminate the key lookup from the previous lesson entirely, and filtered indexes, which shrink an index down to just the rows that actually matter.

Covering Index with INCLUDE

CREATE NONCLUSTERED INDEX IX_OrderLog_Customer_Covering
ON dbo.OrderLog (customer_id)
INCLUDE (order_status, order_total);

-- Fully satisfied by the index — no key lookup needed
SELECT customer_id, order_status, order_total
FROM dbo.OrderLog
WHERE customer_id = 42;

“Covering” means every column the query needs — for filtering, sorting, or just selecting — exists somewhere in the index itself, so the engine never has to jump back to the clustered index at all. This directly eliminates the exact key-lookup cost the previous lesson demonstrated.

Key Columns vs INCLUDE Columns

Put columns you filter/sort on in the key; put columns you only ever SELECT in INCLUDE — this keeps the index narrower and cheaper to maintain than making everything a key column. Key columns also enforce sort order (relevant to ORDER BY), while INCLUDE columns carry no ordering guarantee at all — they’re purely along for the ride.

Filtered Index: Indexing Just a Subset

CREATE NONCLUSTERED INDEX IX_OrderLog_PendingOnly
ON dbo.OrderLog (order_date)
WHERE order_status = 'pending';

Ideal when queries consistently target a small, well-defined subset of a large table — the index is smaller, faster to scan, and cheaper to maintain since it only updates when a matching row changes (a row with order_status = 'completed' never touches this index at all, on insert or update).

-- The optimizer only uses a filtered index when the query's WHERE clause
-- provably matches (or is a subset of) the index's filter condition:
SELECT * FROM dbo.OrderLog WHERE order_status = 'pending' AND order_date > '2026-01-01';
-- Uses IX_OrderLog_PendingOnly — the query's filter is compatible with the index's

SELECT * FROM dbo.OrderLog WHERE order_date > '2026-01-01';
-- Does NOT use it — this query has no order_status filter, so the index can't
-- guarantee it covers every matching row

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