Clustered and Non-Clustered Index in SQL

An index is a disk-based structure linked to a table or view that facilitates quicker row retrieval. A table or view’s table or view’s columns are used to create keys in an index. These keys are kept in a structure (B-tree) that enables SQL Server to quickly and effectively locate the row or rows that correspond to the key values.

Indexes are disk-based structures linked to tables or views, aiding in quicker row retrieval by organizing data in a structured manner. Two primary types of indexes in SQL Server are Clustered and Non-Clustered Indexes.

Clustered and non-clustered indexes are two key types of indexes in relational databases, each with distinct characteristics and use cases.

---

Clustered Index

• Definition: A clustered index determines the physical order of data in the table. The table rows are stored in the order of the index.

• Physical Sorting: The data rows are physically rearranged to match the order of the clustered index.

• One per Table: A table can have only one clustered index because the data can only be physically sorted in one way.

• Efficient for Range Queries: Since the data is sorted, range queries (e.g., BETWEEN, <, >) are faster.

• Example:

  CREATE CLUSTERED INDEX idx_emp_id ON employee(emp_id);
  
  

  In this case, the table rows will be sorted by emp_id.

---

Non-Clustered Index

• Definition: A non-clustered index is a separate structure from the table. It stores a copy of the indexed column(s) along with a pointer to the actual table rows.

• Logical Sorting: The table data is not physically reordered; the index maintains a logical order of the values.

• Multiple per Table: A table can have multiple non-clustered indexes, each optimized for different queries.

• Uses More Storage: Requires additional storage for the index structure.

• Example:

  CREATE NONCLUSTERED INDEX idx_emp_name ON employee(emp_name);
  
  

  This creates an index for emp_name, but the table’s physical order remains unchanged.

---

CLUSTERED INDEX	NON-CLUSTERED INDEX
A clustered index is faster.	A non-clustered index is slower.
The clustered index requires less memory for operations. No additional storage (apart from metadata).	A non-Clustered index requires more memory for operations.
In a clustered index, the clustered index is the main data.	In the Non-Clustered index, the index is the copy of data.
A table can have only one clustered index.	A table can have multiple non-clustered indexes.
The clustered index has the inherent ability to store data on the disk.	A non-Clustered index does not have the inherent ability to store data on the disk.
Rearranges rows to match index order.	Does not affect the table's physical order.
No pointers, as data is part of the index itself.	Includes pointers to the actual data rows.
Primary Keys of the table by default are clustered indexes.	The composite key when used with unique constraints of the table act as the non-clustered index.
Use for columns that are frequently used for sorting, range queries, or primary lookups (e.g., id, date).	Use for columns frequently queried with WHERE, JOIN, or ORDER BY that do not determine the physical order of the data.

Both indexes can complement each other to optimize query performance.