SQL Indexes and Performance 3

Question 1

What is a covering index?

Accepted Answer

An index that includes all columns needed by a specific query

Answer

A covering index includes every column referenced in a query (in SELECT, WHERE, and JOIN clauses), so the query can be resolved from the index alone without accessing the base table.

Question 2

What is the purpose of the INCLUDE clause when creating a non-clustered index in SQL Server?

Accepted Answer

To add non-key columns to the leaf level of the index without making them part of the key

Answer

The INCLUDE clause stores additional non-key columns at the leaf level of a non-clustered index, enabling more queries to be covered without widening the index key itself.

Question 3

What is an index hint in SQL?

Accepted Answer

A directive in a query that forces the optimizer to use a specific index

Answer

An index hint is syntax added to a query that forces the query optimizer to use a specified index, overriding its automatic selection.

Question 4

What does the REBUILD operation on an index do?

Accepted Answer

Drops and recreates the index from scratch, eliminating fragmentation

Answer

REBUILD drops and recreates the index from scratch, fully removing fragmentation and refreshing statistics, though it is resource-intensive.

Question 5

What is a filtered index (also called a partial index)?

Accepted Answer

An index built only on rows that satisfy a specified WHERE condition

Answer

A filtered index is built only on rows matching a specific filter condition, making it smaller and more efficient for queries that include the same filter.

SQL Practice Test

SQL Practice Test

SQL Indexes and Performance 3