Clustered vs. Non-Clustered Index in Databases

Indexes in databases improve query performance by allowing faster data retrieval. There are two primary types of indexes: Clustered Index and Non-Clustered Index.

---

1. Clustered Index

A clustered index determines the physical order of data in the table. Since a table can only have one clustered index, the data is sorted based on this index.

Key Characteristics:

• The table's rows are stored physically in the order of the clustered index.
• Faster for range queries (e.g., BETWEEN, ORDER BY, GROUP BY).
• One clustered index per table, because data can be stored in only one order.
• Usually, the primary key creates a clustered index automatically in most databases.

Example

CREATE CLUSTERED INDEX idx_employee_id ON employees (employee_id);

🔹 Now, the employees table is physically sorted by employee_id.

Performance Impact

• Faster retrieval for queries searching by the indexed column.
• Slower INSERT, UPDATE, and DELETE if the index needs to rearrange rows.

---

2. Non-Clustered Index

A non-clustered index creates a separate structure that maps keys to row locations but does not affect the physical order of data.

Key Characteristics:

• The table is not stored in the order of the index.
• Multiple non-clustered indexes can exist on a table.
• Requires extra storage since it maintains a separate structure.
• Slower than a clustered index for retrieving large ranges of data but faster for lookups on specific values.

Example

CREATE NONCLUSTERED INDEX idx_employee_name ON employees (name);

🔹 Now, queries filtering by name will be optimized.

Performance Impact

• Speeds up SELECT queries on the indexed column.
• Slows down INSERT, UPDATE, and DELETE operations due to index maintenance.

---

Clustered vs. Non-Clustered Index Comparison

Feature	Clustered Index	Non-Clustered Index
Data Storage	Physically reorders table data	Separate index structure
Number Allowed	1 per table	Multiple allowed
Query Speed	Fast for range queries (ORDER BY, BETWEEN)	Fast for specific lookups (WHERE clause)
Insert/Update/Delete Impact	Slower (as it rearranges data)	Slower (additional index structure updates)
Primary Key	Automatically creates a clustered index	Needs to be explicitly created

---

When to Use What?

• Use a clustered index when:

  • Data is frequently queried using range-based searches.
  • The column is a primary key.
  • You want fast retrieval of sorted data.

• Use a non-clustered index when:

  • You need fast lookups on specific columns.
  • The query involves multiple columns in WHERE conditions.
  • There are many JOIN operations on a specific column.

---

Example Query Performance Difference

-- Using Clustered Index
SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 200;

✅ Faster because data is physically sorted.

-- Using Non-Clustered Index
SELECT * FROM employees WHERE name = 'Alice';

✅ Faster because the non-clustered index quickly finds the row.

---

Conclusion

• Clustered Index sorts the actual data, making range queries and sorting operations very efficient.
• Non-Clustered Index speeds up searches for specific values but adds extra storage and maintenance overhead.
• Choosing the right index depends on query patterns, table size, and update frequency.

Would you like me to explain index fragmentation and optimization techniques as well? 🚀