Practice Test GeeksSQL Practice Test
← All SQL Flashcard Decks

Indexes and Performance Flashcards

7 cards from real SQL practice questions. Tap to flip, then mark Knew It or Still Learning — missed cards come back until you master them.

Read the first 7 Indexes and Performance flashcards as text

  1. What is a covering index?

    Answer: An index that includes all columns needed by a specific query

    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.

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

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

    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.

  3. What is an index hint in SQL?

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

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

  4. What does the REBUILD operation on an index do?

    Answer: Drops and recreates the index from scratch, eliminating fragmentation

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

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

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

    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.

  6. Which scenario benefits MOST from a database index?

    Answer: A SELECT with a WHERE clause filtering on an indexed column

    A SELECT filtering on an indexed column directly benefits from the index, enabling the database to locate matching rows with a seek instead of a full table scan.

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

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

    Database statistics describe the distribution of data values in columns and indexes, enabling the query optimizer to estimate row counts accurately and choose efficient execution plans.