How clustered and non-clustered indexes are different
Behzad AhmadIndex
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.
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
Employeetable with the following attributes:Id,Name, andGender. AsIdis 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.
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
Employeetable with the following attributes:Id,Name, andGender. AsIdis 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
Namecolumn (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.
RELATED TAGS
CONTRIBUTOR
Behzad AhmadView all Courses
