Index with included columns in sql server

 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)

 Let’s run the above script

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.