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? 🚀