Covering Index

A covering index contains every column a query needs — both the filter/sort columns and the columns being selected — so the database can answer the query entirely from the index without touching the table.

Normally, once an index finds a matching row, the database still does a lookup back to the table to fetch any columns not in the index (called a "key lookup" or "table access by rowid"). A covering index eliminates that extra step by including the needed columns directly, often via an INCLUDE clause.

It's one of the highest-leverage index tweaks for a hot, read-heavy query — turning two I/O operations per row into one.

-- SQL Server: cover a query that selects last_name but filters on department_id
CREATE INDEX idx_dept_covering
  ON employees (department_id)
  INCLUDE (last_name);