Clustered and Non-Clustered Indexing

Last Updated : 31 Jul, 2025

Indexing is a performance optimization technique in SQL Server that significantly improves the speed of data retrieval operations. There are mainly two types of indexing: Clustered and Non-Clustered. Understanding the difference between them is essential for database developers and administrators to write faster and more efficient queries.

In this article, we'll explore both types of indexes, their characteristics, use cases, and how they affect data storage and access.

Clustered Index

A Clustered Index defines the physical order of rows in a table. When you create a clustered index on a column, SQL Server rearranges the actual data rows to match the index order. This is why a table can have only one clustered index.

A clustered index is created only when both the following conditions are satisfied:

The data can be stored in a sequential or sorted manner.
The column used as the key must contain unique values.

Example of Clustered Index:

Consider a table called Student where the Roll_No column is the primary key. This automatically becomes a clustered index. Here, SQL Server automatically creates a clustered index on the Roll_No column. The rows are physically stored in ascending order based on the Roll_No.

We can have only one clustered index in one table, but we can have one clustered index on multiple columns, and that type of index is called a composite index. Here, the Roll_No column serves as the primary key, automatically becoming the clustered index. The output of querying this table will present data in ascending order of Roll_No.

Non-Clustered Index

A Non-Clustered Index does not change the physical order of data. Instead, it creates a separate structure that stores the indexed column(s) along with pointers (row locators) to the actual rows in the table.

This separation allows you to have multiple non-clustered indexes on a single table, making them useful for columns frequently used in search conditions or joins.

Key Characteristics:

Stores index data separately from the table.
Multiple non-clustered indexes can exist on a table.
Contains a copy of the indexed column(s) and a pointer to the actual data row.

Example of Non-Clustered Index:

In the Student table, we could create a non-clustered index on the Name column. Here, roll no is a primary key, hence there is automatically a clustered index. If we want to apply a non-clustered index in the NAME column (in ascending order), then a new table will be created for that column. In this example, a non-clustered index is created on the Name column. SQL Server will create a separate structure containing Name and pointers to the rows where the corresponding data resides.

Query:

CREATE NONCLUSTERED INDEX NIX_FTE_Name
ON Student (Name ASC);

Output

The row address is used because, if someone wants to search the data for Sudhir, then by using the row address he/she will directly go to that row address and can fetch the data directly.

Differences Between Clustered and Non-Clustered Index

This table organizes the primary differences between clustered and non-clustered indexes, making it easier to understand when to use each index type based on performance requirements and database structure.

Feature	Clustered Index	Non-Clustered Index
Speed	Faster for range-based queries and sorting.	Slower for range-based queries but faster for specific lookups.
Memory Usage	Requires less memory for operations.	Requires more memory due to additional index structure.
Data Storage	The clustered index stores data in the table itself.	The non-clustered index stores data separately from the table.
Number of Indexes per Table	A table can have only one clustered index.	A table can have multiple non-clustered indexes.
Disk Storage	The clustered index can store data on the disk.	The non-clustered index stores the index structure (B-tree) on disk with pointers to the data pages.
Pointer Storage	Stores pointers to the data blocks, not the data itself.	Stores both the indexed value and a pointer to the actual row in a separate data page.
Leaf Nodes	Leaf nodes contain the actual data itself.	Leaf nodes contain indexed columns and pointers to data.
Data Order	Defines the physical order of the rows in the table.	Defines the logical order of data in the index, not the table.
Index Structure	The data is physically reordered to match the index.	The logical order does not match the physical order of rows.
Primary Key	Primary keys are by default clustered indexes.	Composite keys used with unique constraints are non-clustered.
Size	Typically larger, especially for large primary clustered indexes.	Smaller than clustered indexes, especially when composite.
Use Case	Ideal for range queries and sorting.	Suitable for optimizing lookups and queries on non-primary columns.
Impact on Table	A clustered index directly impacts the table's physical storage order.	A non-clustered index does not affect the physical storage order of the table.