SQL Index MCQs

What is the main purpose of an index in SQL?

• Compress tables

• Improve query performance

• Encrypt data

• Create temporary tables

Answer explanation

The main purpose of an index in SQL is to improve query performance by allowing the database to find and retrieve data more quickly, rather than scanning the entire table.

Which type of index determines the physical order of data?

• Non-clustered

• Clustered

• Composite

• Unique

Answer explanation

A clustered index determines the physical order of data in a table, meaning the data rows are stored in the same order as the index. In contrast, a non-clustered index maintains a separate structure, not affecting physical data order.

How many clustered indexes can a table have?

• One

• Two

• Unlimited

• One per column

Answer explanation

A table can have only one clustered index because it defines the physical order of data rows. This unique index ensures that the data is stored in a sorted manner based on the indexed column, making retrieval efficient.

What does a non-clustered index store?

• Actual table data

• Only metadata

• Pointers to rows in the table

• Only nulls

Answer explanation

A non-clustered index does not store actual table data but instead contains pointers to the rows in the table. This allows for faster searches without duplicating the data, making 'Pointers to rows in the table' the correct choice.

Which index is typically used on low-cardinality columns?

• B-Tree Index

• Bitmap Index

• Function-Based Index

• Unique Index

Answer explanation

A Bitmap Index is ideal for low-cardinality columns because it efficiently represents the presence or absence of values using bits, making it suitable for columns with a limited number of distinct values.

What is a composite index?

• Index made up of a foreign key

• Index using multiple columns

• Duplicate index

• Self-joining index

Answer explanation

A composite index is an index that uses multiple columns to improve query performance. This allows for more efficient data retrieval compared to single-column indexes, making 'Index using multiple columns' the correct choice.

What is a B-Tree index?

• Bitmap tree

• Balanced tree structure

• Binary decision tree

• Sorted array

Answer explanation

A B-Tree index is a balanced tree structure that maintains sorted data and allows for efficient insertion, deletion, and search operations. This makes it ideal for database indexing, unlike the other options listed.

What is the benefit of a function-based index?

• Automatically updates

• Indexes on the result of a function

• Optimizes joins

• Requires no refresh

Answer explanation

A function-based index allows you to create an index on the result of a function applied to one or more columns. This can significantly improve query performance when filtering or sorting based on computed values.

Which type of index ensures all values in a column are distinct?

• Clustered

• Bitmap

• Unique

• Composite

Answer explanation

A Unique index ensures that all values in a column are distinct, preventing duplicate entries. This is essential for maintaining data integrity, making 'Unique' the correct choice among the options provided.

What happens if you try to insert a duplicate value in a column with a unique index?

• It is allowed

• It creates a new index

• An error is thrown

• It updates the existing value

Answer explanation

Inserting a duplicate value in a column with a unique index violates the uniqueness constraint, resulting in an error being thrown. Therefore, the correct answer is that an error is thrown.