Index covering message

Index contains all needed columns. Base table will not be read.

This message indicates that an index covers the query. It is printed both for matching and nonmatching scans. Other messages in showplan output help distinguish these access methods:

The next query shows output for a matching scan, using a composite, nonclustered index on au_lname, au_fname, au_id:

select au_fname, au_lname, au_id
from authors
where au_lname = "Williams"
QUERY PLAN FOR STATEMENT 1 (at line 1).

    STEP 1
        The type of query is SELECT.

        FROM TABLE
            authors
        Nested iteration.
        Index : au_names_id
        Forward scan.
        Positioning by key.
        Index contains all needed columns. Base table will not be read.
        Keys are:
            au_lname  ASC
        Using I/O Size 2 Kbytes for index leaf pages.
        With LRU Buffer Replacement Strategy for index leaf pages.

With the same composite index on au_lname, au_fname, au_id, this query performs a nonmatching scan, since the leading column of the index is not included in the where clause:

select au_fname, au_lname, au_id
from authors
where au_id = "A93278"
QUERY PLAN FOR STATEMENT 1 (at line 1).

       STEP 1
        The type of query is SELECT.

        FROM TABLE
            authors
        Nested iteration.
        Index : au_names_id
        Forward scan.
        Positioning at index start.
        Index contains all needed columns. Base table will not be read.
        Using I/O Size 16 Kbytes for index leaf pages.
        With LRU Buffer Replacement Strategy for index leaf pages.

Note that the showplan output does not contain a “Keys are...” message, and the positioning message is “Positioning at index start.” This query scans the entire leaf level of the nonclustered index, since the rows are not ordered by the search argument.