Reasons to use covering indexes I was asked "when do we need to bring covering indexes to play, and why?" by a DBA.

My explanation was:


  It allows engine to pull all of the relevant data directly from the values in the indices themselves. 


My explanation was not satisfactory to the DBA that asked me this question. What other details are relevant? 



I know difference between clustered vs. non-clustered indexes
Clustered Index

This index is the table.  The index information is stored in the b-tree pages while all of the other columns (those not in the index) are stored at the leaf level of the index.  All other indexes will have a link back to this one and will always include the indexed columns of the clustered index at their leaf level.  This is because if SQL uses a non-clustered index and needs additional information it will use that value to look back into the clustered index for the additional index.  There can be only one clustered index (obviously) because as I said above it actually is the table.

Non-Clustered Index

The indexed values are also located in the b-tree pages but in this case the only data located at the leaf level is the link back to the clustered index and any columns specified by the INCLUDE clause.  In the case of a UNIQUE index there may also be an additional, virtual, column stored at the leaf level called the uniqueifer.

Covering Index

As @KrisGruttemeyer said this is a functional term not structural (like the other two).  This is just a non-clustered index that contains all of the required columns (either in the index or at the leaf level) to satisfy the query. Don't forget that all indexes include the clustered index columns so if you have a query that requires Cols1-4 and Id (the clustered index) and you only have an index that is indexed on Col1 and includes Col2, Col3 and Col4 it will still be covering because the Id column is going to be included since it's the clustered index. Edit to answer the Why part of the question: Because you don't have to make that extra lookup to the clustered index a covering index can be much faster.  The smallest index that fits the query and covers it will probably be the fastest.  Of course you also have to take into account the overhead of having indexes vs the benefit you get for how many queries but that goes way beyond what can be put here.

Just for completeness Heaps

A heap is simply a table that does not have a clustered index.  The page layout is somewhat different from a clustered index but that is beyond the scope of this question.

&nbsp;

And because I always include this when talking about Clustered Indexes 
The Primary Key

The primary key is a special case of a unique index.  There can be only one and it may be clustered or not.  It has nothing to do with the clustered index but a lot of people get confused and think the primary key is always clustered.