SQL Server Interview Question #228

What is the difference between key columns and INCLUDE columns?

Advanced Indexing Senior Advanced

Detailed Explanation

Key columns define the logical ordering of a nonclustered index and can participate directly in seek and ordered-access behavior. INCLUDE columns are stored only at the leaf level and do not determine the B-tree's key ordering.

Columns needed for WHERE, JOIN, or ORDER BY may belong in the key depending on the query pattern. Columns needed only to return data can often be placed in INCLUDE to create a covering index without unnecessarily widening the key.

Included columns still consume storage and write resources.

Code Example

CREATE INDEX IX_Orders_Customer_Date
ON dbo.Orders(CustomerId, OrderDate)
INCLUDE (TotalAmount, StatusId);