Clustered and Non-Clustered Indexing

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.

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.

Explaination:

• A table can have only one clustered index.
• A clustered index can be created on multiple columns → called a composite index.
• In this case, Roll_No is the primary key, so it automatically becomes the clustered index.
• Query results are displayed 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:

Explanation:

• Creates Index: Makes a non-clustered index named NIX_FTE_Name.
• Table: The index is created on the Student table.
• Column: Applied to the Name column.
• Order: Data in the index is stored in ascending order.
• Purpose: Speeds up searches/queries on the Name column without changing the table’s actual data order.

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.

Clustered Index in DBMS

Visit Course

Clustered Index in DBMS

Non Clustered Index in DBMS