Covering index in sql server

 A covering index is an index which is made up of all (or more) of the columns required to satisfy a query as key columns of the index. When a covering index can be used to execute a query, fewer IO operations are required since the optimizer no longer has to perform extra lookups to retrieve the actual table data. This can significantly improve query performance by reducing the amount of I/O required.

Covering index includes all columns referenced in a query, either in the SELECT, WHERE, JOIN, or ORDER BY clauses. Since the index contains all the necessary data, the database engine can retrieve the results directly from the index, avoiding the need to access the table. This reduces I/O operations and speeds up the query. Covering indexes reduce disk I/O, which is often the slowest part of query execution.

We have table in that we have around 1.2 CR records. Currently we don’t have any index on this table.

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

Running this query

See the execution time : 4971ms

Logical Read: 101270 pages read

IO Cost : 75.0172

Now creating Covering index.

CREATE NONCLUSTERED INDEX ix_covering_non_SalesOrderDetail_demo ON dbo.SalesOrderDetail_demo (ProductID,OrderQty,SpecialOfferID,UnitPrice,UnitPriceDiscount)

 Now running the query

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

 Execution time: 87 ms

Logical read: 183 pages

IO Cost: 0.133664

Let’s see if we have not included one column in the index, see the performance of this query. Here we have removed OrderQty from the index.

CREATE NONCLUSTERED INDEX ix_covering_non_SalesOrderDetail_demo ON dbo.SalesOrderDetail_demo (ProductID,SpecialOfferID,UnitPrice,UnitPriceDiscount)

Now running the same query.

Execution time: 4997ms

Logical read: 101270 pages

IO Cost: 75.0172

Be careful when we are creating the non-cluster index.