SQL Indexes and Performance

Question 1

What is the primary purpose of an index in SQL?

Accepted Answer

To speed up data retrieval operations

Answer

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?

Accepted Answer

Clustered index

Answer

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?

Accepted Answer

Only one

Answer

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?

Accepted Answer

Key values and pointers to the data row locations

Answer

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?

Accepted Answer

CREATE INDEX idx_name ON employees (last_name)

Answer

The correct syntax is CREATE INDEX followed by the index name, the ON keyword, the table name, and the column(s) to index.

SQL Practice Test