Covering Index

An index that answers the whole query by itself

A covering index is a composite/multi-column index that happens to include every column a specific query needs — both the columns used for filtering AND the columns being selected/returned. When this is true, the database engine can answer the entire query directly from the index structure itself, without ever touching the actual table rows.

A concrete example

Suppose you frequently run:

sql
SELECT id, department FROM students WHERE department = 'CS';

If you create:

sql
CREATE INDEX idx_dept_covering ON students (department, id);

This index contains both department (the filter column) and id (the selected column). When this query runs, the engine finds the matching department = 'CS' entries in the index — and since id is also sitting right there in the same index leaf, it can return the result directly from the index, skipping the "bookmark lookup" back to the actual table row entirely (recall 20.6's extra hop for non-clustered indexes).

This is sometimes described as an "index-only scan" — the engine literally never touches the table's heap/clustered storage for this query.

Why this is a real performance win

Without covering, a non-clustered index lookup requires: (1) search the index for matching keys, then (2) for each match, hop back to the actual row to fetch any additional needed columns. That second step means extra I/O — potentially one additional page read per matching row. A covering index eliminates step (2) entirely for that specific query shape, which can be a dramatic speedup, especially when many rows match.

It's query-specific, not universal

A covering index only "covers" queries whose exact combination of filtered and selected columns are all present in the index. The same table might need SELECT * elsewhere, which a narrow covering index (with only 2-3 columns) obviously cannot satisfy — that query would still need the full row.

Edge cases

• Adding many columns to make an index "cover" more queries increases the index's size and write-maintenance cost (20.14) — covering is a targeted optimization for known, important, frequently-run query shapes, not something to apply indiscriminately.
• Some engines support explicitly "included" (non-key) columns in an index — columns stored in the index for covering purposes but not used for sorting/filtering, which can be more storage-efficient than making them full key columns.
• If even one selected or filtered column is missing from the index, the engine falls back to the bookmark-lookup approach (or a full scan) for that query — covering is all-or-nothing per query.

Interview-style Q&A

Q: What makes an index a "covering index" for a given query? A: The index contains every column that query filters on AND every column it selects/returns, so the engine can answer entirely from the index without touching the table's row storage.

Q: What specific overhead does a covering index eliminate? A: The extra "bookmark lookup" hop back from a non-clustered index entry to the actual table row, which a covering index makes unnecessary since all needed data is already in the index.

Key takeaway: a covering index is a targeted trade — bigger index (more columns) in exchange for eliminating an entire step of I/O for a specific, important query pattern.