⚡ Indexes in DBMS

---

1. What is an Index?

👉 Like a book’s table of contents → helps the database find data faster.
Goal: Speed up queries.

---

2. Clustered Index

• Definition: Rows are physically stored & sorted in the order of the index.
• Example: Phonebook arranged by last name.
• Key Points:
  • ✅ Only one per table.
  • ✅ Usually created on Primary Key by default.
  • ✅ Best for range queries (IDs 100–200).
  • ❌ Slower when updating (data rearrangement).

---

3. Non-Clustered Index

• Definition: Creates a separate index structure with pointers to rows.
• Example: Book index at the back → points to page numbers.
• Key Points:
  • ✅ Multiple allowed per table.
  • ✅ Best for exact lookups (Find salary of ID = 105).
  • ❌ Updates slower (pointer maintenance).

---

4. Clustered vs Non-Clustered Index

Feature	Clustered Index 🗂️	Non-Clustered Index 📑
Data Storage	Stores data physically sorted	Stores pointers to rows
Number per Table	Only one	Many allowed
Search Speed	Faster for ranges	Faster for exact matches
Update Performance	Slower (data rearranged)	Slower (pointers updated)
Common Use Case	Primary Key	Columns like Name, Salary

---

5. Quick Examples

• Clustered Index:
  Table sorted by ID.
  Query: SELECT * FROM Students WHERE ID BETWEEN 10 AND 20;
• Non-Clustered Index:
  Index on Name.
  Query: SELECT * FROM Students WHERE Name = 'John';

---

✅ Key takeaway for exams:

• Clustered → 1 per table, physical order, best for ranges.
• Non-Clustered → many allowed, pointer-based, best for exact lookups.

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

3. 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.
4. How many clustered indexes can a table have?
   • A) One
   • B) Two
   • C) Unlimited
   • D) None
     Answer: A) One
5. 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.
6. 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

7. 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.
8. 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)
9. 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

10. 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.
11. 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
12. 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

13. 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
14. 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
15. 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

16. 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.
17. 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.
18. 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.”

---