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

To enforce data uniqueness constraints

Answer

To define table relationships

Answer

To compress data storage

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

Non-clustered index

Answer

Full-text index

Answer

Bitmap index

Question 3

How many clustered indexes can a single table have?

Accepted Answer

Only one

Answer

Unlimited

Answer

One per indexed column

Answer

Up to 10

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 full copy of all table data

Answer

Only NULL column values

Answer

Foreign key relationship metadata

Question 5

Which SQL statement correctly creates a new index on a table?

Accepted Answer

CREATE INDEX idx_name ON employees (last_name)

Answer

ADD INDEX idx_name ON employees (last_name)

Answer

BUILD INDEX idx_name FOR employees (last_name)

Answer

MAKE INDEX idx_name ON employees (last_name)

Question 6

What is a composite (compound) index?

Accepted Answer

An index built on two or more columns of the same table

Answer

An index on a computed or derived column

Answer

An index that spans multiple tables

Answer

An index that stores multiple data type formats

Question 7

What happens to a table's indexes when a new row is inserted?

Accepted Answer

Indexes are automatically updated by the database engine

Answer

Indexes are dropped and rebuilt automatically

Answer

Indexes must be manually refreshed by the DBA

Answer

Indexes remain unchanged until an explicit REBUILD is run