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

An index that spans multiple related tables

Answer

An index that covers NULL values for nullable columns

Answer

Another name for a primary key index

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

To include the index in the query plan cache automatically

Answer

To include foreign key references in the index definition

Answer

To specify which stored procedures should use the index

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

A suggestion generated by the database to create a missing index

Answer

An automatic optimization the database applies to slow queries

Answer

A comment in DDL code explaining why an index was created

Question 4

What does the REBUILD operation on an index do?

Accepted Answer

Drops and recreates the index from scratch, eliminating fragmentation

Answer

Adds new columns to an existing index definition

Answer

Converts a non-clustered index into a clustered index

Answer

Scans the index pages looking for data corruption

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

An index that only indexes a subset of columns in the table

Answer

An index that is partially built and not yet usable

Answer

An index defined on a derived or computed column

Question 6

Which scenario benefits MOST from a database index?

Accepted Answer

A SELECT with a WHERE clause filtering on an indexed column

Answer

SELECT * FROM large_table with no WHERE clause

Answer

An UPDATE statement that modifies every row in the table

Answer

An INSERT of 10,000 rows in a single batch

Question 7

What are database statistics and why are they important for index performance?

Accepted Answer

Metadata about data value distribution used by the optimizer to estimate query costs

Answer

Logs of query execution times stored for auditing purposes

Answer

Counters tracking how many times each index has been used by queries

Answer

Records of all INSERT, UPDATE, and DELETE operations on a table