Composite Keys, Included Columns, Covering Queries, and Key Lookup Tradeoffs

Learning outcomes

After choosing a sane clustering key, ServiceHub still needs query-specific access paths. A composite nonclustered index can seek by multiple columns, satisfy an ORDER BY, and carry additional leaf-only columns through INCLUDE. Those capabilities make covering plans possible, but every extra byte is copied into index pages and must be maintained on writes. This lesson uses plan shape and I/O evidence to balance Key Lookup cost against index bloat.

1. Composite keys are ordered search structures

In a B+ tree on (status, opened_at, work_order_id), rows are ordered first by status, then by opened_at within each status, then by work_order_id. That order can efficiently support a predicate on status, or on status plus a date range. It generally cannot seek directly on opened_at alone without another useful prefix or optimizer strategy. “Put the most selective column first” is therefore incomplete; predicate shape and desired ordering matter.

USE ServiceHubLab;GOIF SCHEMA_ID(N'lab09') IS NULL EXEC(N'CREATE SCHEMA lab09 AUTHORIZATION dbo;');DROP TABLE IF EXISTS lab09.WorkOrderSearch;CREATE TABLE lab09.WorkOrderSearch(  work_order_id bigint IDENTITY(1,1) NOT NULL,  customer_id int NOT NULL,  status varchar(20) NOT NULL,  priority tinyint NOT NULL,  opened_at datetime2(3) NOT NULL,  assigned_technician_id int NULL,  summary nvarchar(300) NOT NULL,  diagnostic_notes nvarchar(1000) NULL,  CONSTRAINT PK_WorkOrderSearch PRIMARY KEY CLUSTERED(work_order_id));;WITH n AS( SELECT TOP (30000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.all_objects a CROSS JOIN sys.all_objects b)INSERT lab09.WorkOrderSearch(customer_id,status,priority,opened_at,assigned_technician_id,summary,diagnostic_notes)SELECT 1+n%2500,       CASE WHEN n%20=0 THEN 'ESCALATED' WHEN n%4=0 THEN 'CLOSED' ELSE 'OPEN' END,       1+n%5,       DATEADD(minute,n,'2026-01-01T00:00:00'),       NULLIF(n%200,0),       CONCAT(N'Work order ',n),       CASE WHEN n%10=0 THEN REPLICATE(N'x',400) ENDFROM n;GOCREATE INDEX IX_WorkOrderSearch_StatusOpenedON lab09.WorkOrderSearch(status,opened_at);GO

2. Observe the lookup before removing it

If a query seeks the nonclustered index but also needs columns absent from that index, SQL Server may perform a Key Lookup into the clustered index. For a small number of qualifying rows, repeated lookups can be cheaper than scanning a much wider index. As qualifying row count increases, there is a cost crossover—often called a lookup tipping point—where another access path may become cheaper. That point is not a fixed percentage.

SET STATISTICS IO ON;SELECT work_order_id,customer_id,status,opened_at,       assigned_technician_id,summaryFROM lab09.WorkOrderSearchWHERE status='ESCALATED'  AND opened_at >= '2026-01-10T00:00:00'ORDER BY opened_at;SET STATISTICS IO OFF;GO

Run the statement with an actual execution plan in SSMS or VS Code MSSQL. Inspect whether SQL Server chooses IX_WorkOrderSearch_StatusOpened, whether a Key Lookup appears, and how many rows flow through it. The exact operator choice depends on current data distribution and statistics; the lab teaches evidence collection rather than promising a fixed plan.

3. INCLUDE can cover without making every column a key

Included columns are stored at the nonclustered leaf level and are not used to navigate the B+ tree. They are therefore suitable for projected columns that must be returned but are not useful search/sort keys. Microsoft also excludes included columns from the key-column count and key-byte limits, although wide included values still consume storage, memory, I/O, and write maintenance.

DROP INDEX IX_WorkOrderSearch_StatusOpenedON lab09.WorkOrderSearch;GOCREATE INDEX IX_WorkOrderSearch_StatusOpenedON lab09.WorkOrderSearch(status,opened_at)INCLUDE(customer_id,assigned_technician_id,summary);GOSET STATISTICS IO ON;SELECT work_order_id,customer_id,status,opened_at,       assigned_technician_id,summaryFROM lab09.WorkOrderSearchWHERE status='ESCALATED'  AND opened_at >= '2026-01-10T00:00:00'ORDER BY opened_at;SET STATISTICS IO OFF;GO

The clustering key work_order_id is already carried by this nonunique nonclustered index, so it need not be repeated in INCLUDE. Compare local reads and plan shape before/after. Do not manufacture a universal “N reads saved” statement.

4. Wrong approach: cover every query

A tempting tuning loop is to add every projected column to every index until lookups disappear. That can create wider pages, lower cache density, more log records, longer rebuilds, and higher DML cost. Including nvarchar(max), varbinary(max), xml, or similarly large values can be particularly expensive because leaf storage grows dramatically.

SELECT i.name,       SUM(ps.used_page_count)*8.0/1024 AS used_mb,       SUM(ps.row_count) AS rowsFROM sys.indexes AS iJOIN sys.dm_db_partition_stats AS ps  ON ps.object_id=i.object_id AND ps.index_id=i.index_idWHERE i.object_id=OBJECT_ID(N'lab09.WorkOrderSearch')GROUP BY i.nameORDER BY used_mb DESC;GOSELECT i.name,u.user_seeks,u.user_scans,u.user_lookups,u.user_updatesFROM sys.indexes AS iLEFT JOIN sys.dm_db_index_usage_stats AS u  ON u.database_id=DB_ID() AND u.object_id=i.object_id AND u.index_id=i.index_idWHERE i.object_id=OBJECT_ID(N'lab09.WorkOrderSearch');GO

user_updates counts operations, not rows modified, and usage counters reset on engine startup/database shutdown conditions. Lesson 5 will turn that limitation into an observation-window discipline.

5. Production judgment and cleanup

Keep key columns focused on predicates, joins, grouping/order requirements, and uniqueness semantics. Use included columns to cover valuable queries only when the measured read benefit outweighs storage/write/maintenance cost. Validate representative parameter values: a covering index that helps one highly selective query can still be poor for another parameter range.

DROP TABLE IF EXISTS lab09.WorkOrderSearch;GO

Authoritative references

• Index architecture and design guide — composite and included-column guidance
• Create indexes with included columns — covering semantics and limits
• sys.dm_db_index_usage_stats — usage/update counters and reset semantics
• SQL Server 2025 build versions — servicing baseline