How clustered and non-clustered indexes are different

Index

An index is a key made of one or more than one database columns. It efficiently fetches rows from the database. It is used to find the row associated with the index value quickly as well.

There are two types of indexes:

  • Clustered
  • Non-clustered

Clustered index

The clustered index sorts the data rows in the database depending on their key values. We can have only one clustered index per table. In the SQL server, the clustered index is automatically created on the primary key column.

Diagram of a clustered index

Code

create table Employee
( Id int primary key,
Name varchar(50),
Gender varchar(30)
);
insert into Employee
values (4, 'Behzad', 'Male' );
insert into Employee
values (3, 'Ahmad', 'Male' );
insert into Employee
values (5, 'Naina', 'Female' );
SELECT * FROM Employee

Explanation

  • Lines 1-5: We create the Employee table with the following attributes: Id, Name, and Gender. As Id is the primary key, it will be automatically used as the clustered index.

  • Lines 7-14: We insert the data into the table.

  • Line 17: We display the whole data of the table.

Non-clustered index

A non-clustered index stores data and indices at different locations. The index contains a pointer that locates the data. We can have multiple non-clustered indexes per table.

A diagram of a non-clustered index

Code

create table Employee
( Id int primary key,
Name varchar(50),
Gender varchar(30)
);
insert into Employee
values (2, 'Behzad', 'Male' );
insert into Employee
values (3, 'Ahmad', 'Male' );
insert into Employee
values (1, 'Naina', 'Female' );
-- Retrieve data before applying non clustered index
SELECT * FROM Employee
-- creating non clustered index
create nonclustered index NIX_FTE_Name
on Employee (Name ASC) INCLUDE(Gender);
-- Retrieve data after applying non clustered index
SELECT * FROM Employee

Explanation

  • Lines 1-5: We create the Employee table with the following attributes: Id, Name, and Gender. As Id is the primary key, it will be automatically used as the clustered index.

  • Lines 7-14: We insert the data into the table.

  • Lines 19-20: We apply the non-clustered index for the Name column (in ascending order). Then, we create a new table for that column.

  • Line 22: We retrieve the whole data of the table after applying the non-clustered index.

Here are the main differences between clustered and non-clustered indexes.

Clustered


It is only created on the primary key.


The clustered index’s size is comparatively large.


We can have only one clustered index in one table .


It’s data retrieval speed is faster than a non-clustered index.


It requires less memory to perform operations.


Non-clustered


We can create it on any key.


It’s size is comparatively small.


We can have multiple non-clustered indexes in one table.


It’s data retrieval speed is slower than a clustered index.


It requires more memory to perform operations.


Free Resources