All 7 Indexes questions
More in Indexes
What is an index in SQL Server, and how does it help speed up queries? What is the difference between a clustered index and a nonclustered index? How do you identify unused or duplicate indexes that might be safely removed to improve write performance? How do you decide which columns to choose, and in what order, when creating a composite index covering multiple columns? How does index fragmentation happen over time, and how do you address it? How do filtered indexes work, and what specific scenarios benefit most from using one?
Advertisement
SQL Server › Indexes

What is a covering index, and how does it help avoid an expensive lookup operation?

Intermediate

Answer

A covering index includes all the columns a specific query needs, either as key columns or as included columns, meaning SQL Server can satisfy the entire query directly from the index itself without needing to go back to the actual table to retrieve additional column values, which is a common source of performance improvement.
CREATE NONCLUSTERED INDEX IX_Orders_Covering
ON Orders(CustomerId)
INCLUDE (OrderDate, Amount);
Real-world example A reporting query that filters by CustomerId and selects OrderDate and Amount runs significantly faster after a covering index is created that includes those exact columns, avoiding the need to look up additional data from the main table.

Common follow-ups: What is the difference between key columns and included columns in an index?;How do you identify which queries would benefit from a covering index?

Query Optimization & Plans;Columnstore Indexes
All 7 Indexes questions