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