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.
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
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.
And because I always include this when talking about
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.