What Is a Nonclustered and Included-Column Indexes in Microsoft SQL Server?
A separate B-tree stores key columns and a row locator. INCLUDE columns are leaf payload that may cover reads without changing key order.
How the Nonclustered and Included-Column Indexes Algorithm Works
A separate B-tree stores key columns and a row locator. INCLUDE columns are leaf payload that may cover reads without changing key order.
When to Use Nonclustered and Included-Column Indexes
Selective filters/joins and narrow projections that otherwise cause many key lookups.
How to Create a Microsoft SQL Server Nonclustered and Included-Column Indexes
Review data quality, existing structures, feature support, build time, and deployment requirements before creating an index on a production table.
CREATE INDEX IX_Orders_CustomerDate ON dbo.Orders (CustomerId, CreatedAt DESC) INCLUDE (Total);Nonclustered and Included-Column Indexes Query Example
This example demonstrates the matching query shape. Check the execution plan to confirm that the engine can use the index.
SELECT CreatedAt, Total FROM dbo.Orders WHERE CustomerId = 42 ORDER BY CreatedAt DESC;Queries Supported by Nonclustered and Included-Column Indexes
Selective filters/joins and narrow projections that otherwise cause many key lookups. A similar-looking predicate may not use the same access path; operators, expressions, key order, and data type all matter.
Read Performance, Storage, and Write Costs
Included values enlarge leaf pages and increase write/cache costs. Indexes reduce some search work but require storage and add maintenance to writes.
Nonclustered and Included-Column Indexes Limitations and Common Mistakes
Included columns cannot seek, order, or enforce uniqueness. Key/data type restrictions apply.
Do not assume that an index being present means the optimizer chose it or that the query became selective.
How to Measure and Tune Nonclustered and Included-Column Indexes
Use actual plans and Query Store to see whether lookup reduction justifies costs.
Compare representative plans and timings before and after, including concurrent writes and realistic data volumes.
Nonclustered and Included-Column Indexes Questions
Will the database always use this index?
No. The optimizer weighs selectivity, estimated costs, statistics, cache state, and the amount of data returned.
What should I verify before production?
Confirm version support, query compatibility, write overhead, build/rollback procedures, and index behavior during backup and recovery.
Official Microsoft SQL Server Index Documentation
Check Microsoft Learn: SQL Server Indexes for release-specific requirements and supported operators.