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:
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.
create table Employee( Id int primary key,Name varchar(50),Gender varchar(30));insert into Employeevalues (4, 'Behzad', 'Male' );insert into Employeevalues (3, 'Ahmad', 'Male' );insert into Employeevalues (5, 'Naina', 'Female' );SELECT * FROM Employee
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.
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.
create table Employee( Id int primary key,Name varchar(50),Gender varchar(30));insert into Employeevalues (2, 'Behzad', 'Male' );insert into Employeevalues (3, 'Ahmad', 'Male' );insert into Employeevalues (1, 'Naina', 'Female' );-- Retrieve data before applying non clustered indexSELECT * FROM Employee-- creating non clustered indexcreate nonclustered index NIX_FTE_Nameon Employee (Name ASC) INCLUDE(Gender);-- Retrieve data after applying non clustered indexSELECT * FROM Employee
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.
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.
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.