Indexes and performance
Composite keys, INCLUDE and covering
Column order depends on query shape.
Understand the concept
Composite index keys are ordered. For CustomerId equality plus an OrderDate range, (CustomerId, OrderDate) is a useful candidate. This is more contextual than always putting the most selective column first. Without a predicate on the leading key, access may be less selective, though a scan can still be chosen.
INCLUDE columns do not define key order but can provide leaf-level values needed by a query. Covering is query-specific: accessible required columns can avoid lookups. Excessive INCLUDE columns increase size and write costs. A lookup may be appropriate for few rows and expensive for many. Compare actual plans, logical reads and maintenance costs.
Support repeated customer-and-date queries.
Treating an included column as an ordering key.
One topic. Three levels of depth.
SELECT OrderId, OrderDate, Status FROM lab.SalesOrder WHERE CustomerId = 1 ORDER BY OrderDate;Defines the workload before choosing an index.
This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.
Try it yourself first.
Find customer two’s March 2026 orders using a half-open range.
Show the solution and explanation
SELECT OrderId, OrderDate, Status FROM lab.SalesOrder WHERE CustomerId = 2 AND OrderDate >= '20260301' AND OrderDate < '20260401';Returns 108. This is a candidate for a customer-date index, not a speed guarantee.