Covering index
A covering index is an index containing all columns needed to evaluate and return a query, avoiding access to the base table.
100 SQL Server Mistakes and How to Avoid Them
A covering index is an index containing all columns needed to filter, join, and return a query's results, thereby avoiding a lookup of the base table.
- How it is used
- Optimizes narrowly defined queries by returning required columns directly from a nonclustered index.
- How deep it goes
- Explains key and included columns, lookup avoidance, schema fragility, and write costs through concrete SQL examples.
- Examples
- Demonstrates SalesOrderHeaders filtered by SalesOrderDate and CustomerContacts ordered by email.
- What you can do after
- Apply it via SQL Server by choosing index keys and INCLUDE columns for a specific query, while weighing read gains against write overhead.
Read how 100 SQL Server Mistakes and How to Avoid Them explains covering index →
Just Use Postgres!
A covering index is an index that stores all columns needed by a query, allowing qualifying results to be returned without reading the table rows.
- How it is used
- Optimizes frequently executed queries by returning projected values directly from index storage.
- How deep it goes
- Explains INCLUDE, execution-plan changes, and write-maintenance trade-offs, stopping short of broader index administration.
- Examples
- Demonstrates a player-statistics query and compares bitmap heap execution with an index-only plan.
- What you can do after
- Apply it via PostgreSQL CREATE INDEX and verify reduced heap access with EXPLAIN ANALYZE.
Related concepts
- indexingkind of · Indexing is the organization or use of positions, keys, or lookup structures to identify and retrieve selected data or elements.
- select starcontrasts with · SELECT * is SQL projection syntax that requests every column from the selected table, relation, or other query data source.
- index-only scanused by · An index-only scan is a database access technique that answers a query from index contents without fetching corresponding table rows when visibility permits.
- sorting algorithmaddresses · A sorting algorithm is a procedure that arranges elements of a collection into an order determined by a specified criterion.
These guides are generated from the text of Manning books by Manning's knowledge graph and link to the sections they draw on. The full books are available on liveBook and at manning.com.