Q26: What is a Covering Index, and how does Index Seek differ from Index Scan?
- Index Seek: The database engine uses index tree structures to jump directly to specific matching rows. Highly efficient.
- Index Scan: The engine traverses the entire index leaf structure row by row. Less efficient than a seek.
- Covering Index: A non-clustered index that includes all columns referenced by a query (filtering, joining, and selected output columns). It allows the engine to return results directly from the index without reading table data pages.
Creating a Covering Index with Included Columns
-- Covering index including extra payload columns
CREATE INDEX idx_orders_customer_covering
ON orders (customer_id, order_date)
INCLUDE (total_amount, order_status);
-- Executing query directly satisfied by index
SELECT total_amount, order_status
FROM orders
WHERE customer_id = 4521
AND order_date >= '2026-01-01';💡 Because all selected and filtered columns exist within the index, the query engine skips reading table data pages entirely.
Key Takeaways
- Index Seeks locate target records directly; Index Scans read through entire index structures.
- Covering indexes avoid expensive table lookup operations by serving queries entirely from index pages.