Indexes MCQ Practice Test

Q1. Which of the following is a characteristic of a clustered index in a database?

• A. It is a type of non-clustered index.
• B. It stores the data in a sorted order. ✓
• C. It is used for query optimization.
• D. It is used for data compression.

Q2. Which index type is most suitable for simple equality lookups (WHERE x = value) in databases that support it?

• A. A hash index ✓
• B. A bitmap index
• C. A full-text index
• D. A spatial index

Q3. What is the primary purpose of a clustered index in a database?

• A. To improve query performance by reducing the number of disk I/O operations
• B. To increase data redundancy and improve data integrity
• C. To speed up data retrieval by grouping related data together ✓
• D. To reduce the size of the database by eliminating redundant data

Q4. In a database with a large table containing a column of employee IDs and a column of corresponding salaries, which of the following index types would most likely improve query performance when executing a query that filters employees by a specific salary range?

• A. A clustered index on the salary column
• B. A non-clustered index on the employee ID column
• C. A non-clustered index on the salary column with a included employee ID column ✓
• D. A hash index on the salary column

Q5. What is the primary benefit of creating a clustered index on a column used in the WHERE clause of a frequently executed query?

• A. It reduces the number of I/O operations required to retrieve data
• B. It improves data compression and reduces storage costs
• C. It enables faster sorting and grouping of data
• D. It optimizes the order of data storage for faster query performance ✓

Q6. A database index is created on a column with a high cardinality, but the column contains a large number of null values. Which of the following is a potential issue with this index?

• A. The index may lead to a significant increase in storage space, as each null value requires additional storage.
• B. The index may cause a decrease in query performance, as the database needs to spend more time handling the null values. ✓
• C. The index may lead to a significant improvement in query performance, as the database can quickly eliminate rows with null values.
• D. The index may cause a significant increase in disk I/O, as the database needs to read the null values to determine their existence.

Q7. In a relational database, what is the primary difference between a clustered index and a non-clustered index on a table with a large number of rows?

• A. A clustered index rearranges the physical order of the rows, while a non-clustered index does not. ✓
• B. A clustered index allows for faster range queries, while a non-clustered index is optimized for equality queries.
• C. A clustered index is always created on a primary key column, while a non-clustered index can be created on any column.
• D. A clustered index can be created on a table with a large number of rows, while a non-clustered index is limited to smaller tables.

Q8. A database has two indexes, one on the "name" column and another on the "age" column. Which of the following queries would benefit most from the use of an index on the "age" column?

• A. SELECT * FROM customers WHERE name = 'John';
• B. SELECT * FROM customers WHERE age > 18; ✓
• C. SELECT * FROM customers WHERE name LIKE '%John%';
• D. SELECT * FROM customers WHERE age BETWEEN 18 AND 65;

Q9. Which of the following index types is best suited for a column that frequently participates in WHERE and JOIN clauses, and contains a large number of unique values?

• A. Clustered index
• B. Non-clustered index with included columns ✓
• C. Composite index
• D. Covering index

Q10. In a database with a composite index on columns A and B, which of the following queries will benefit from the index?

• A. SELECT * FROM table WHERE A = 5
• B. SELECT * FROM table WHERE B = 10
• C. SELECT * FROM table WHERE A = 5 AND B = 10 ✓
• D. SELECT * FROM table WHERE A = 5 OR B = 10

That was just a sample. Sign up to unlock the full question bank with timed tests and certificates.