An index with included columns is a type of non-clustered index that allows us to include additional columns in the index that are not part of the index key. These included columns are stored in the leaf level of the index and are used to cover specific queries without increasing the size of the index key, which can help optimize query performance.
There are couple benefits to using included
columns. It gives us the ability to include column types that are not allowed
as index keys in our index. Also, when all the columns in our query are either
an index key or included column, the query no longer has to do an extra lookup
in order to get all the data needed to satisfy the query which results in fewer
disk operations.
See the below example
|
select ProductID ,Sum(OrderQty) as OrderQty,sum(SpecialOfferID) as SpecialOfferID ,sum(UnitPrice) as UnitPrice,sum(UnitPriceDiscount) as UnitPriceDiscount from SalesOrderDetail_demo where productID=750 group by ProductID order by ProductID |
In this query we are using ProductID for filter
and from remaining column we are getting the value. Here we are creating the
non-cluster index on the productID and we will includes the all column.
See the index below.
|
CREATE NONCLUSTERED INDEX
ix_included_non_SalesOrderDetail_demo ON dbo.SalesOrderDetail_demo (ProductID) INCLUDE (OrderQty,SpecialOfferID,UnitPrice,UnitPriceDiscount) |
Execution time: 89 ms
Logical Read: 181 pages
IO Cost:.133664
An index with included columns in SQL Server is a powerful tool for optimizing query performance. It allows you to create a no clustered index with additional columns included at the leaf level, enabling the index to cover more queries while keeping the index key smaller and more efficient. This approach is particularly beneficial for complex queries that access multiple columns but don't need those columns to be part of the index key.
