What is a covering index?
Quick Answer
A covering index contains every column a query needs — in the key or in the INCLUDE list — so the query can be answered entirely from the index without going back to the table. This eliminates key lookups, which are the usual reason a non-clustered index seek is still slow.
A covering index is one that has all the columns a particular query touches, so SQL Server can satisfy the whole query from the index and never has to read the base table. That matters because a non-clustered index seek that's missing a column does a key lookup for every matching row, and lots of key lookups is a common reason a query is still slow even though it's “using the index.” You cover it by putting the filter columns in the key and the output-only columns in the INCLUDE list.
- Query:
SELECT CustomerId, Total FROM Orders WHERE Status = 1. - Index
ON Orders(Status)— seeks, but then does a key lookup per row to fetchCustomerIdandTotal. - Index
ON Orders(Status) INCLUDE (CustomerId, Total)— covering; no lookup. INCLUDEcolumns live only in the leaf level, don't affect key ordering, and don't count toward the ~900-byte key limit.- Trade-off: a wide covering index is more to store and maintain — cover the queries that matter, not everything.