Quick Answer
Key lookups happen when a non-clustered index satisfies the WHERE clause, but the SELECT needs columns not in the index. SQL Server fetches those from the clustered index — one random I/O per row. For 100 rows it's fine; for 10,000 rows it's painful. Fix: add INCLUDE columns to the non-clustered index to cover the query.
Answer
Key lookups occur when a non-clustered index lacks required columns. SQL must fetch missing columns from the clustered index for each qualifying row. With many rows, this becomes slow and I/O-heavy. Solutions include covering indexes or query redesign.
S
SugharaIQ Editorial Team
Verified Answer
This answer has been peer-reviewed by industry experts holding senior engineering roles to ensure technical accuracy and relevance for modern interview standards.
