What are Covering Indexes and Covered Queries in SQL Server?
A covered query is a query where all the columns in the query's result set are pulled from non-clustered indexes.
A query is made into a covered query by the judicious arrangement of indexes.
A covered query is often more performant than a non-covered query in part because non-clustered indexes have more rows per page than clustered indexes or heap indexes, so fewer pages need to be brought into memory in order to satisfy the query. They have more rows per page because only part of the table row is part of the index row.
A covering index is an index which is used in a covered query. There is no such thing as an index which, in and of itself, is a covering index. An index may be a covering index with respect to query A, while at the same time not being a covering index with respect to query B.
A Covering Index is a Non-Clustered index. Both Clustered and Non-Clustered indexes use B-Tree data structure to improve the search for data, the difference is that in the leaves of a Clustered Index a whole record (i.e. row) is stored physically right there!, but this is not the case for Non-Clustered indexes. The following examples illustrate it:
Example: I have a table with three columns: ID, Fname and Lname.
However, for a Non-Clustered index, there are two possibilities: either the table already has a Clustered index or it doesn't:
As the two diagrams show, such Non-Clustered indexes do not provide a good performance, because they cannot find the favorite value (i.e. Lname) solely from the B-Tree. Instead they have to do an extra Look Up step (either Key or RID look up) to find the value of Lname. And, this is where covered index comes to the screen. Here, the Non-Clustered index on ID coveres the value of Lname right next to it in the leaves of the B-Tree and there is no need for any type of look up anymore.
A covering index is one which can satisfy all requested columns in a query without performing a further lookup into the clustered index.
There is no such thing as a covering query.
Have a look at this Simple-Talk article: Using Covering Indexes to Improve Query Performance.
If all the columns requested in the select list of query, are available in the index, then the query engine doesn't have to lookup the table again which can significantly increase the performance of the query. Since all the requested columns are available with in the index, the index is covering the query. So, the query is called a covering query and the index is a covering index.
A clustered index can always cover a query, if the columns in the select list are from the same table.
The following links can be helpful, if you are new to index concepts: