SQL Indexes and Performance 1 — 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.
SQL Indexes and Performance 1
What is the primary purpose of an index in SQL?