What is a covering index
A covering index is an index which satisfies all the columns being used in a query without performing a further lookup either to the clustered index or to the base table. It contains all the columns whether they are used in SELECT, JOIN, WHERE, GROUP BY, or ORDER BY clause of the query. Covering index works like a pseudo-clustered index for the query. It improves the performance of the query by adding non-key columns in the index which help the query optimizer to remove any lookup which can be a key lookup or a RID lookup. A key lookup is performed in case the base table accommodates a clustered index otherwise a RID lookup is used by the query optimizer to fetch the non-key columns needed by the query. To know more about key lookup and RID lookup, click here.
The optimizer doesn’t need … More