Covering index concept in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a covering index in a database?

A covering index is an index that contains all the columns needed to answer a query, so the database can get the data directly from the index without looking up the main table.

beginner

Why does a covering index improve query performance?

Because it avoids accessing the main table, reducing disk reads and speeding up data retrieval.

intermediate

Which columns should be included in a covering index?

All columns that appear in the SELECT, WHERE, JOIN, or ORDER BY clauses of the query should be included in the covering index.

beginner

True or False: A covering index can sometimes be larger than a regular index.

True. Because it includes more columns to cover the query, it can be bigger in size.

intermediate

How does a covering index differ from a regular index?

A regular index may only include key columns for searching, while a covering index includes all columns needed to satisfy the query without accessing the table.

What is the main benefit of using a covering index?

A It compresses the data to save disk space.

B It allows the query to be answered using only the index without accessing the main table.

C It automatically updates the table schema.

D It encrypts the data for security.

A covering index contains all columns needed for the query, so the database does not need to read the main table.

Which columns should be included in a covering index?

A Only the primary key columns.

B Only columns that are rarely used.

C Only columns with numeric data.

D All columns used in SELECT, WHERE, JOIN, or ORDER BY clauses.

To cover a query, the index must include all columns the query needs.

True or False: Using a covering index always reduces the size of the index.

A False

B True

C Only if the table is small

D Only if the index is unique

Covering indexes can be larger because they include more columns.

What happens if a query uses a covering index?

A The database locks the entire table.

B The database ignores the index and reads the table.

C The database reads data only from the index.

D The database rebuilds the index.

The query can be answered directly from the index without accessing the table.

Which of these is NOT a reason to create a covering index?

A To encrypt sensitive data.

B To reduce the number of disk reads.

C To include all columns needed by a query.

D To speed up queries by avoiding table lookups.

Covering indexes do not encrypt data; they improve query speed.

Explain what a covering index is and how it helps improve query performance.

Think about how the database can answer queries faster by using only the index.

Describe how to decide which columns to include in a covering index for a given query.

Consider all parts of the query that need data.