SQL Indexes and Performance — Questions and Answers
Question 1: What is the primary purpose of an index in SQL?
- To speed up data retrieval operations (Correct answer)
- To enforce data uniqueness constraints
- To define table relationships
- To compress data storage
Correct answer: To speed up data retrieval operations
Indexes improve query performance by allowing the database to locate rows faster without scanning the entire table.
Question 2: Which type of index is automatically created when you define a PRIMARY KEY constraint in most relational databases?
- Non-clustered index
- Full-text index
- Clustered index (Correct answer)
- Bitmap index
Correct answer: Clustered index
A clustered index is automatically created on the primary key column, physically ordering the table's data rows by that key.
Question 3: How many clustered indexes can a single table have?
- Unlimited
- One per indexed column
- Up to 10
- Only one (Correct answer)
Correct answer: Only one
A table can have only one clustered index because data rows can only be physically sorted in one order on disk.
Question 4: What does a non-clustered index store to locate actual data rows?
- A full copy of all table data
- Only NULL column values
- Key values and pointers to the data row locations (Correct answer)
- Foreign key relationship metadata
Correct answer: Key values and pointers to the data row locations
A non-clustered index stores key column values and row locators (pointers) that reference the actual data rows in the table.
Question 5: Which SQL statement correctly creates a new index on a table?
- ADD INDEX idx_name ON employees (last_name)
- CREATE INDEX idx_name ON employees (last_name) (Correct answer)
- BUILD INDEX idx_name FOR employees (last_name)
- MAKE INDEX idx_name ON employees (last_name)
Correct answer: CREATE INDEX idx_name ON employees (last_name)
The correct syntax is CREATE INDEX followed by the index name, the ON keyword, the table name, and the column(s) to index.
Question 6: What is a composite (compound) index?
- An index on a computed or derived column
- An index that spans multiple tables
- An index built on two or more columns of the same table (Correct answer)
- An index that stores multiple data type formats
Correct answer: An index built on two or more columns of the same table
A composite index is built on two or more columns of the same table, allowing efficient queries that filter on multiple columns together.
Question 7: What happens to a table's indexes when a new row is inserted?
- Indexes are dropped and rebuilt automatically
- Indexes must be manually refreshed by the DBA
- Indexes are automatically updated by the database engine (Correct answer)
- Indexes remain unchanged until an explicit REBUILD is run
Correct answer: Indexes are automatically updated by the database engine
The database engine automatically maintains all indexes whenever rows are inserted, updated, or deleted to keep query results accurate.
Question 8: What is a query execution plan?
- A schedule for running automated queries
- The sequence of steps the database engine uses to execute a query (Correct answer)
- A list of all indexes in a database
- A backup strategy for query results
Correct answer: The sequence of steps the database engine uses to execute a query
A query execution plan describes the operations (scans, seeks, joins, sorts) the database engine will perform to retrieve or modify data for a given query.
Question 9: Which keyword is used in MySQL and PostgreSQL to display a query's execution plan?
- ANALYZE
- EXPLAIN (Correct answer)
- DESCRIBE
- SHOWPLAN
Correct answer: EXPLAIN
The EXPLAIN keyword in MySQL and PostgreSQL displays the execution plan, revealing how the optimizer intends to process the query.
Question 10: What is a 'full table scan' in SQL?
- An index rebuild operation across the whole table
- Reading every row in a table to find matching records (Correct answer)
- A query that selects all columns with SELECT *
- A backup of the entire table's data
Correct answer: Reading every row in a table to find matching records
A full table scan reads every row in the table to find matching records, which is inefficient on large tables that lack a useful index.
Question 11: What is index selectivity?
- The ability to manually choose which index a query uses
- The ratio of unique values in an indexed column to the total number of rows (Correct answer)
- The order in which multiple indexes are applied to a query
- The physical size of an index measured in bytes
Correct answer: The ratio of unique values in an indexed column to the total number of rows
Index selectivity measures how many unique values exist relative to total rows; higher selectivity (more distinct values) makes an index more efficient at filtering rows.
Question 12: Which column type typically makes the WORST candidate for an index?
- A primary key column with millions of unique values
- A foreign key column used in JOIN conditions
- A boolean column with only TRUE or FALSE values (Correct answer)
- A datetime column used in range filters
Correct answer: A boolean column with only TRUE or FALSE values
A boolean column with only two distinct values has very low selectivity, so the index eliminates very few rows and the optimizer often ignores it in favor of a table scan.
Question 13: What is the role of the query optimizer in a database system?
- A tool that rewrites SQL statements into stored procedures
- A component that evaluates multiple execution strategies and selects the most efficient plan (Correct answer)
- A process that compresses query results before returning them
- A feature that permanently caches the results of frequently run queries
Correct answer: A component that evaluates multiple execution strategies and selects the most efficient plan
The query optimizer analyzes possible execution plans (different index choices, join orders, etc.) and selects the one estimated to be most efficient.
Question 14: What is index fragmentation?
- An index spread across multiple physical files
- An index with missing or corrupted entries
- Disorder in index pages caused by ongoing insertions, updates, and deletes (Correct answer)
- An index that still references deleted rows
Correct answer: Disorder in index pages caused by ongoing insertions, updates, and deletes
Index fragmentation occurs when frequent data modifications cause index pages to fall out of logical order, increasing I/O and degrading query performance.
Question 15: What is a covering index?
- An index that includes all columns needed by a specific query (Correct answer)
- An index that spans multiple related tables
- An index that covers NULL values for nullable columns
- Another name for a primary key index
Correct 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.
Question 16: What is the purpose of the INCLUDE clause when creating a non-clustered index in SQL Server?
- To include the index in the query plan cache automatically
- To add non-key columns to the leaf level of the index without making them part of the key (Correct answer)
- To include foreign key references in the index definition
- To specify which stored procedures should use the index
Correct 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.
Question 17: What is an index hint in SQL?
- A suggestion generated by the database to create a missing index
- A directive in a query that forces the optimizer to use a specific index (Correct answer)
- An automatic optimization the database applies to slow queries
- A comment in DDL code explaining why an index was created
Correct 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.
Question 18: What does the REBUILD operation on an index do?
- Drops and recreates the index from scratch, eliminating fragmentation (Correct answer)
- Adds new columns to an existing index definition
- Converts a non-clustered index into a clustered index
- Scans the index pages looking for data corruption
Correct 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.
Question 19: What is a filtered index (also called a partial index)?
- An index that only indexes a subset of columns in the table
- An index built only on rows that satisfy a specified WHERE condition (Correct answer)
- An index that is partially built and not yet usable
- An index defined on a derived or computed column
Correct 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.
Question 20: Which scenario benefits MOST from a database index?
- SELECT * FROM large_table with no WHERE clause
- An UPDATE statement that modifies every row in the table
- A SELECT with a WHERE clause filtering on an indexed column (Correct answer)
- An INSERT of 10,000 rows in a single batch
Correct 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.
Question 21: What are database statistics and why are they important for index performance?
- Logs of query execution times stored for auditing purposes
- Metadata about data value distribution used by the optimizer to estimate query costs (Correct answer)
- Counters tracking how many times each index has been used by queries
- Records of all INSERT, UPDATE, and DELETE operations on a table
Correct 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.
What is the primary purpose of an index in SQL?
What Practice Test Geeks adds beyond an AI chat for SQL
An AI chatbot can quiz you, but it can't reproduce the exam conditions or keep your record. Here you get:
- A timed, full-length SQL mock exam — 78 questions in 120 minutes with a 63% pass mark, modeled on the official exam's question count and time limit, followed by a score report broken down by exam domain.
- A fixed SQL question bank: the same test every attempt, so your score is comparable from one try to the next instead of changing with every regenerated quiz.
- Progress saved on your device: completed tests, scores and missed questions stay in My Progress, so you can retake only what you got wrong.
- A printable SQL cheat sheet you can print or save as a PDF.
- SQL flashcards built from the same question bank.
These practice questions were written with AI tools. They are not real exam questions. We have not yet matched this exam's facts to an official source, so confirm fees, dates and rules with the exam body. Not reviewed by a subject-matter expert. Editorial policy