Covering index in Sql Server

A covering index is the most efficient method for retrieving data from a table.

Covering index in Sql Server
A covering index is the most efficient method for retrieving data from a table.

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.

A "Covering Index" fully covers the query since it provides all the necessary information to resolve the query. the query's SELECT, JOIN, and WHERE clauses all make reference to columns in the covering index.

Covering indexes incur higher costs when table data is modified; hence, they should not be created for every query type. This is frequently one of the final optimisations after all other measures have been implemented.



The following TSQL query can now be executed by only accessing the new index we just created since all columns in the query are part of the index.