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) |
|
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 |
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.
