What are Covering Indexes and Covered Queries in SQL Server?

Question

Can you explain the concepts of, and relationship between, Covering Indexes and Covered Queries in Microsoft's SQL Server?

Answer 1

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.

Answer 2

A covering query is on where all the predicates can be matched using the indices on the underlying tables.

This is the first step towards improving the performance of the sql under consideration.

Answer 3

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.

Answer 4

Here's an article in devx.com that says:

Creating a non-clustered index that contains all the columns used in a SQL query, a technique called index covering

I can only suppose that a covered query is a query that has an index that covers all the columns in its returned recordset. One caveat - the index and query would have to be built as to allow the SQL server to actually infer from the query that the index is useful.

For example, a join of a table on itself might not benefit from such an index (depending on the intelligence of the SQL query execution planner):

PersonID ParentID Name
1        NULL     Abe
2        NULL     Bob
3        1        Carl
4        2        Dave

Let's assume there's an index on PersonID,ParentID,Name - this would be a covering index for a query like:

SELECT PersonID, ParentID, Name FROM MyTable

But a query like this:

SELECT PersonID, Name FROM MyTable LEFT JOIN MyTable T ON T.PersonID=MyTable.ParentID

Probably wouldn't benifit so much, even though all of the columns are in the index. Why? Because you're not really telling it that you want to use the triple index of PersonID,ParentID,Name.

Instead, you're building a condition based on two columns - PersonID and ParentID (which leaves out Name) and then you're asking for all the records, with the columns PersonID, Name. Actually, depending on implementation, the index might help the latter part. But for the first part, you're better off having other indexes.