What is a Covered Index?

Question

I've just heard the term covered index in some database discussion - what does it mean?

Answer 1

A covering index is an index that contains all, and possibly more, the columns you need for your query.

For instance, this:

SELECT *
FROM tablename
WHERE criteria

will typically use indexes to speed up the resolution of which rows to retrieve using criteria, but then it will go to the full table to retrieve the rows.

However, if the index contained the columns column1, column2 and column3, then this sql:

SELECT column1, column2
FROM tablename
WHERE criteria

and, provided that particular index could be used to speed up the resolution of which rows to retrieve, the index already contains the values of the columns you're interested in, so it won't have to go to the table to retrieve the rows, but can produce the results directly from the index.

This can also be used if you see that a typical query uses 1-2 columns to resolve which rows, and then typically adds another 1-2 columns, it could be beneficial to append those extra columns (if they're the same all over) to the index, so that the query processor can get everything from the index itself.

Here's an article: Index Covering Boosts SQL Server Query Performance on the subject.

Answer 2

Covering index is just an ordinary index. It's called "covering" if it can satisfy query without necessity to analyze data.

example:

CREATE TABLE MyTable
(
  ID INT IDENTITY PRIMARY KEY, 
  Foo INT
) 

CREATE NONCLUSTERED INDEX index1 ON MyTable(ID, Foo)

SELECT ID, Foo FROM MyTable -- All requested data are covered by index

This is one of the fastest methods to retrieve data from SQL server.

Answer 3

@aku. A nitpick, but since your table definition includes a primary key, which creates a clustered index on that column (ID), adding the ID column to the non-clustered index is redundant. The clustered key is automatically added to any non-clustered index.

-Edoode