Difference between Clustered Index and Non-Clustered Index in SQL with Examples

In the world of relational databases, indexes play a crucial role in enhancing query performance by providing efficient data access. Among the various types of indexes, two primary types are Clustered Index and Non-Clustered Index. In this article, we will delve into the differences between these two index types and understand their unique characteristics through practical examples.

Clustered Index: 

A clustered index determines the physical order of data in a table. It defines the table's structure by sorting and storing the data rows based on the indexed column(s). Each table can have only one clustered index, and it directly affects the table's physical organization.

Example of Clustered Index: Consider a table called "Employees" with columns: EmployeeID (Primary Key), Name, Age, and Department. If we create a clustered index on the EmployeeID column, the data rows will be stored in the table based on the ascending or descending order of the EmployeeID values. In this case, the data will be physically organized based on the EmployeeID column's values.

Non-Clustered Index: 

A non-clustered index is a separate structure from the actual data that contains a copy of the indexed columns along with a pointer to the corresponding data rows. Unlike a clustered index, a table can have multiple non-clustered indexes. Non-clustered indexes do not dictate the physical order of data in the table.

Example of Non-Clustered Index: Continuing with the "Employees" table example, let's create a non-clustered index on the "Department" column. This index will contain a copy of the Department column's values and pointers to the corresponding data rows. The data in the table will remain physically organized based on the clustered index (if present) or in an unordered manner. The non-clustered index on the Department column will provide faster lookup and retrieval of data based on the Department values.

Let's consider an "Employee" table with the following structure:

Table structure:

sql
CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Department VARCHAR(50),
    Salary DECIMAL(10, 2)
)

Example 1: Clustered Index Let's create a clustered index on the EmployeeID column.

Creating a clustered index:

sql
CREATE CLUSTERED INDEX IX_Employee_EmployeeID ON Employee (EmployeeID)

In this case, the data rows in the "Employee" table will be physically organized based on the ascending or descending order of the EmployeeID values. The clustered index directly affects the table's physical organization.

Example 2: Non-Clustered Index Continuing with the "Employee" table example, let's create a non-clustered index on the Department column.

Creating a non-clustered index:

swl
CREATE NONCLUSTERED INDEX IX_Employee_Department ON Employee (Department)

The non-clustered index on the Department column will contain a copy of the Department values and pointers to the corresponding data rows. The data in the table will remain physically organized based on the clustered index (if present) or in an unordered manner. The non-clustered index will provide faster lookup and retrieval of data based on the Department values.

Example 3: Multiple Non-Clustered Indexes Let's create multiple non-clustered indexes on different columns of the "Employee" table for improved query performance.

Creating non-clustered indexes:

sql
CREATE NONCLUSTERED INDEX IX_Employee_LastName ON Employee (LastName)
CREATE NONCLUSTERED INDEX IX_Employee_Salary ON Employee (Salary)

In this example, we have created two non-clustered indexes on the LastName and Salary columns. Each non-clustered index contains a copy of the respective indexed column values and pointers to the corresponding data rows. These indexes will enhance the performance of queries that involve filtering or sorting based on these columns.

Key Differences between Clustered and Non-Clustered Index:

1. Physical Organization: Clustered index determines the physical order of data in the table, while non-clustered index does not affect the physical order.
2. Structure: Clustered index is part of the table structure, while non-clustered index is a separate structure.
3. Unique vs. Non-Unique: Clustered index is always unique as it is based on the table's primary key, while non-clustered index can be either unique or non-unique.
4. Data Duplication: Clustered index does not involve data duplication, whereas non-clustered index contains a copy of the indexed column(s) along with pointers to data rows.
5. Performance: Clustered index provides faster retrieval of entire rows, especially when selecting multiple columns. Non-clustered index excels in faster lookup based on indexed columns.

In summary, the clustered index determines the physical organization of data in a table, while non-clustered indexes provide faster data retrieval through separate index structures. Understanding the differences between these two index types is essential for effective database design and optimizing query performance. By choosing the appropriate index type based on the specific requirements of your data and queries, you can significantly enhance the overall efficiency and speed of your SQL database operations.