Clustered vs Non-Clustered Indexes: Fundamentals Quiz Quiz

Explore the essential differences and characteristics of clustered and non-clustered indexes in databases. This quiz is designed to reinforce your understanding of key concepts, usage scenarios, and structural distinctions between these two index types.

1. Primary Structure Question

   Which statement best describes a clustered index in a database table?

   1. A clustered index is always stored separately from the table data.
   2. A clustered index determines the physical order of data rows in the table.
   3. A clustered index is only used in temporary tables.
   4. A clustered index can contain duplicate key values.

   Explanation: A clustered index organizes and sorts the actual data rows of the table based on its key. This means the physical arrangement of data matches the index order. In contrast, non-clustered indexes are stored separately and only reference the data rows. Clustered indexes must contain unique key values by default and are not limited to temporary tables.

2. Count Limit Question

   How many clustered indexes can you create on a single table?

   1. One
   2. Unlimited
   3. Two
   4. One per column

   Explanation: A table can have only one clustered index because the data rows can be stored in only one order. Multiple clustered indexes or one per column are not possible since each would require a different storage order. Although you can have many non-clustered indexes, the clustered index is limited to one.

3. Non-Clustered Storage Question

   What is a key characteristic of a non-clustered index?

   1. It can only be created on primary key columns.
   2. It rearranges the data order in the table.
   3. It is the same as a clustered index, but faster.
   4. It stores a separate structure containing a copy of indexed columns and pointers to the actual data rows.

   Explanation: Non-clustered indexes create and maintain a separate structure that stores copies of indexed columns and pointers to find the associated table rows. This allows quick lookups without changing the table's data order. Unlike clustered indexes, they don't affect row storage, can be placed on any eligible column, and are not simply faster versions of clustered indexes.

4. Performance Scenario Question

   If you frequently run range queries on a 'date' column, which index type is generally more efficient?

   1. Missing index
   2. Clustered index
   3. Non-clustered index
   4. Fake index

   Explanation: Clustered indexes are very effective for range queries because the data is physically stored in index order, allowing fast retrieval of consecutive rows. Non-clustered indexes can help, but may require additional lookups. ‘Missing index’ is not a valid type, and ‘fake index’ does not exist, making them incorrect.

5. Uniqueness Rule Question

   Which of the following is true regarding uniqueness in clustered indexes by default?

   1. Clustered indexes require unique key values.
   2. Clustered indexes must always have NULL values.
   3. Clustered indexes are never unique.
   4. Clustered indexes ignore duplicate entries.

   Explanation: Clustered indexes typically enforce uniqueness, either through the primary key or by adding a unique identifier behind the scenes. They do not require NULL values, and uniqueness cannot be ignored. Clustered indexes are not inherently non-unique, making the other statements incorrect.

6. Non-Clustered Quantity Question

   What is the typical limit on the number of non-clustered indexes you can create on a single table?

   1. Exactly two
   2. You cannot use non-clustered indexes
   3. Dozens or more, limited mainly by system resources
   4. Only one

   Explanation: You can create many non-clustered indexes on a single table, often dozens or more, depending on the system’s specifications and performance requirements. Unlike clustered indexes, which are limited to one, non-clustered indexes can be added as needed. The answers suggesting one, two, or none are incorrect.

7. Data Retrieval Question

   When using a non-clustered index, how does the database engine typically locate the actual data row?

   1. By searching the entire table for the value
   2. By reordering the table before searching
   3. By following a pointer from the index to the row in the table
   4. By duplicating the table data in the index

   Explanation: Non-clustered indexes store pointers that direct the engine to the exact location of the data row, making retrieval efficient. The database does not search the whole table or reorder data before searching. It also does not duplicate all table data in the index, making the distractors incorrect.

8. Key Inclusion Question

   Which index type is typically associated with including non-key columns for faster retrieval of query results?

   1. Clustered index
   2. No index
   3. Full-text index
   4. Non-clustered index

   Explanation: Non-clustered indexes allow the inclusion of non-key columns to improve performance when queries need additional columns, reducing lookups to the base table. Clustered indexes include all columns by definition as they are the data. Full-text indexes serve different search purposes, and 'No index' is not applicable.

9. Primary Key Default Index Question

   If you create a primary key on a table without specifying the index type, which index is generally created by default?

   1. Hashed index
   2. Clustered index
   3. Sorted index
   4. Non-clustered index

   Explanation: By default, creating a primary key results in a clustered index unless the system already has one, in which case it may use a non-clustered index. Hashed and sorted indexes are not default types for a primary key. Therefore, clustered index is the commonly default choice.

10. Update Operation Question

    Which index type may require more overhead to maintain during frequent updates, especially on large tables?

    1. Imaginary index
    2. Non-clustered index
    3. Clustered index
    4. Mistyped index

    Explanation: Clustered indexes can incur more maintenance overhead during updates because changing indexed values may force data rows to be physically reordered. Non-clustered indexes are less impacted since their structure is separate. Imaginary and mistyped indexes do not exist.