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
What is the primary purpose of an index in SQL?
Answer: To speed up data retrieval operations
Indexes improve query performance by allowing the database to locate rows faster without scanning the entire table.
Which type of index is automatically created when you define a PRIMARY KEY constraint in most relational databases?
Answer: Clustered index
A clustered index is automatically created on the primary key column, physically ordering the table's data rows by that key.
How many clustered indexes can a single table have?
Answer: Only one
A table can have only one clustered index because data rows can only be physically sorted in one order on disk.
What does a non-clustered index store to locate actual data rows?
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.
Which SQL statement correctly creates a new index on a table?
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.
What is a composite (compound) index?
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.
What happens to a table's indexes when a new row is inserted?
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.