Clustered and Non-Clustered Indexes

1. What is an Index?

  • An index makes searching data faster, like a book’s table of contents.
    Goal: Improve query speed in a database.

2. Clustered Index

  • Definition: Sorts and stores rows physically in the table based on the index key.
    • Example: A phonebook sorted by last name.
    • Table data is arranged in the same order as the index.
Key Points:
  1. One per table: A table can only have one clustered index.
  2. Faster for range queries: Good for searching ranges like “Find all employees between IDs 100 and 200.”
  3. Primary Key: By default, the primary key of a table is a clustered index.

3. Non-Clustered Index

  • Definition: Creates a separate structure with pointers to the actual rows in the table.
    • Example: An index at the end of a book that points to page numbers.
Key Points:
  1. Multiple per table: A table can have many non-clustered indexes.
  2. Faster for specific searches: Good for queries like “Find the salary of employee ID 105.”
  3. Slower updates: Maintaining pointers makes updates slower.

4. Differences Between Clustered and Non-Clustered Indexes

FeatureClustered IndexNon-Clustered Index
Data StorageStores data physically in orderStores pointers to actual rows
Number Per TableOnly oneMany
Search SpeedFaster for rangesFaster for exact matches
Update PerformanceSlower (data rearrangement)Slower (pointer updates)
Example Use CasePrimary keyColumns like names or salaries

5. Key Points to Remember

  1. Clustered Index:
    • Data is physically sorted.
    • Only one clustered index per table.
    • Best for range queries.
  2. Non-Clustered Index:
    • Separate structure with pointers to rows.
    • Many non-clustered indexes allowed.
    • Best for specific lookups.

6. Quick Examples

  • Clustered Index:
    • Table sorted by ID (Primary Key).
    • Query: “Get all rows where ID > 10 and ID < 20.”
  • Non-Clustered Index:
    • Index on Name column.
    • Query: “Find the row where Name = ‘John’.”

MCQ

1. Basics of Indexes

  1. What is the primary purpose of an index in a database?
    • A) To increase storage capacity.
    • B) To speed up query performance.
    • C) To ensure data integrity.
    • D) To create backups of the database.
      Answer: B) To speed up query performance.
  2. Which of the following best describes an index in a database?
    • A) A table with duplicate data.
    • B) A mechanism to optimize query execution.
    • C) A process to reorganize database structure.
    • D) A method to control access permissions.
      Answer: B) A mechanism to optimize query execution.

2. Clustered Index

  1. In a clustered index, how is data stored?
    • A) Randomly across disk pages.
    • B) Physically sorted in the table.
    • C) Stored as pointers to rows.
    • D) Compressed and encoded.
      Answer: B) Physically sorted in the table.
  2. How many clustered indexes can a table have?
    • A) One
    • B) Two
    • C) Unlimited
    • D) None
      Answer: A) One
  3. Which of the following is an example of a good use case for a clustered index?
    • A) Searching for specific employee salaries.
    • B) Finding all orders placed between two dates.
    • C) Searching for all customers with a specific name.
    • D) Counting total rows in a table.
      Answer: B) Finding all orders placed between two dates.
  4. By default, which key is assigned a clustered index in a table?
    • A) Foreign Key
    • B) Composite Key
    • C) Primary Key
    • D) Candidate Key
      Answer: C) Primary Key

3. Non-Clustered Index

  1. Where does a non-clustered index store its data?
    • A) Within the rows of the table.
    • B) In a separate structure with pointers to the rows.
    • C) Directly in memory for faster access.
    • D) Compressed in a binary format.
      Answer: B) In a separate structure with pointers to the rows.
  2. How many non-clustered indexes can a table have?
    • A) Only one
    • B) Two
    • C) Unlimited (practically limited by system constraints)
    • D) None
      Answer: C) Unlimited (practically limited by system constraints)
  3. Which query benefits the most from a non-clustered index?
    • A) Retrieving all rows where the ID is greater than 10.
    • B) Looking up the salary of an employee by their name.
    • C) Finding all rows in a specific range.
    • D) Retrieving all columns in a table.
      Answer: B) Looking up the salary of an employee by their name.

4. Differences Between Clustered and Non-Clustered Index

  1. Which of the following statements is true for a clustered index?
    • A) It stores pointers to the data rows.
    • B) The table’s data is stored in the same order as the index.
    • C) Multiple clustered indexes can exist per table.
    • D) It is ideal for searching non-primary key columns.
      Answer: B) The table’s data is stored in the same order as the index.
  2. Which index type is better for exact lookups?
    • A) Clustered Index
    • B) Non-Clustered Index
    • C) Composite Index
    • D) Full-Text Index
      Answer: B) Non-Clustered Index
  3. In terms of performance, a clustered index is faster for:
    • A) Updating columns frequently.
    • B) Retrieving data in a specific range.
    • C) Performing exact searches.
    • D) Running DELETE statements.
      Answer: B) Retrieving data in a specific range.

5. Scenario-Based Questions

  1. You have a table with millions of rows, and users often search for employee IDs in a range. What type of index should you use on the EmployeeID column?
    • A) Clustered Index
    • B) Non-Clustered Index
    • C) Full-Text Index
    • D) No Index
      Answer: A) Clustered Index
  2. A query frequently searches for specific customer names in a database. Which index would you apply to the Name column?
    • A) Clustered Index
    • B) Non-Clustered Index
    • C) No Index
    • D) Composite Index
      Answer: B) Non-Clustered Index
  3. Which type of index would be best for a column used in frequent sorting and range queries?
    • A) Clustered Index
    • B) Non-Clustered Index
    • C) Bitmap Index
    • D) Hash Index
      Answer: A) Clustered Index

6. Advanced Concepts

  1. Which of the following can cause slower updates in a non-clustered index?
    • A) Data reorganization in the table.
    • B) Frequent pointer updates to the actual rows.
    • C) Locking all rows during updates.
    • D) Using the index for SELECT queries.
      Answer: B) Frequent pointer updates to the actual rows.
  2. A table has a clustered index on the primary key. What happens when you insert a row with a duplicate primary key?
    • A) The database automatically creates a new clustered index.
    • B) The row is added without error.
    • C) The insertion is rejected due to primary key constraints.
    • D) The table is re-indexed.
      Answer: C) The insertion is rejected due to primary key constraints.
  3. A clustered index is most suitable for which type of query?
    • A) “Find all rows with Name = ‘John’.”
    • B) “Retrieve all rows sorted by creation date.”
    • C) “Search for the total count of rows.”
    • D) “Find all rows where column data is null.”
      Answer: B) “Retrieve all rows sorted by creation date.”