What are unique, clustered, and non-clustered indexes in SQL?

Unique, clustered, and non-clustered indexes in SQL

Indexes are created on column(s) of tables or views View is a virtual table based on the result of a SELECT statement.Viewsand allow quick search in a table based on the values of that column(s). They allow the SQL server engine to go through each record of a table. This is based on the index key without scanning all rows of the table. In SQL, we can create, drop, alter, and rename indexes without any effect on the data of table/view. Both unique and non-unique column(s) can be used to create an index.

Structure of index

An index is a hierarchy of nodes organized in a B-TreeB-Tree is a self-balancing tree in which nodes are sorted in inorder traversal. structure. The top node is referred to as the root node and the bottom nodes are called leaf nodes. When we search in an index, we start from the root node and after a traversal of all intermediate nodes, we reach the leaf nodes. Data stored in the leaf nodes depends on the type of index.

Structure of Index

Types of indexes

In the SQL server, indexes are divided into two categories:

  • Clustered index
  • Non-clustered index

In a non-clustered index, a pointer to the actual data is stored at the leaf level. However, actual data is stored at the leaf level of a clustered index.

Code example

create table Person(
  Id int,
  Name varchar(50),
  Gender varchar(1)
);
Insert into Person values(11,'Asma','F');
Insert into Person values(3,'Rameen','F');
Insert into Person values(77,'Wahab','M');
Insert into Person values(38,'Saad','M');
select * from Person

Explanation

  • Lines 1–5: We create the Person table in SQL.
  • Line 2: We initialize an Id column of type integer.
  • Line 3: We initiate a Name column of type varchar.
  • Line 4: We initiate a Gender column of type varchar.
  • Lines 7–10: We insert records into Person table using the Insert statement.
  • Line 12: We print all records of the Person table.

Note: The output of the code above will produce results in no particular order.

Clustered index

When a primary keyPrimary key is set of column(s) that identifies each row in a table uniquely. exists in a table, a clustered index is automatically created. Data in a table/view is ordered and stored according to the clustered index column. This is why each table/view can only have one clustered index since data can only be sorted in one order. Additionally, clustered indexes are faster as they require less memory for operations. This is because leaf nodes of a clustered index store actual data and not the row identifiers.lwqelqwe kl;ekekqwl k;lke;q

Moreover, we can create clustered indexes on both unique and non-unique columns. To create clustered index on a column other than the primary key column, we have to drop the previous index first.

Structure of clustered index

Code example

create table Person(
  Id int,
  Name varchar(50),
  Gender varchar(1)
);
Insert into Person values(11,'Asma','F');
Insert into Person values(3,'Rameen','F');
Insert into Person values(77,'Wahab','M');
Insert into Person values(38,'Saad','M');
ALTER TABLE Person
ADD PRIMARY KEY (Id);
select * from Person

Explanation

  • Lines 1–10: We create Person table in SQL and insert records into the table using the Insert statement.
  • Lines 12–13: We add primary key constraint on the Id column.
  • Line 15: We print all records of the Person table.

Note: In this example, when the primary key constraint is added, a clustered index is automatically created. The output of this code will produce in increasing order of Id.

Non-clustered index

A non-clustered index is a logical index and doesn’t order data in a table/view according to the column(s) on which the index is built. They improve the performance of frequently used queries that are not handled by a clustered index. However, a non-clustered index is slower since the first index is searched for pointers of data. Next, a lookup is performed on a clustered index.

Each table/view can have multiple non-clustered indexes. Additionally, a Non-clustered index can be defined on a unique and non-unique column(s).

Structure of non-clustered index

Code example

create table Person(
 Id int,
 Name varchar(50),
 Gender varchar(1)
);
Insert into Person values(11,'Asma','F');
Insert into Person values(3,'Rameen','F');
Insert into Person values(77,'Ali','M');
Insert into Person values(38,'Wahab','M');
create index NON_CLUSTERED_INDEX_NAME
on Person (Name ASC); 
Select * from Person where Name = 'Wahab'

Explanation

  • Lines 1–10: We create a Person table in SQL and insert record into the table using the Insert statement.
  • Lines 12–13: We create a non-clustered index on the Name column.
  • Line 15: We create a non-clustered index to fetch records based on the Name column.

Unique index

When a unique constraintUnique constraint restricts identical entries in a column(s). exists in a table, a unique non-clustered index is automatically created. Only one null value can be added in a column on which a unique index is defined. Unique indexes prevent duplicate entries by ensuring the uniqueness of values in a column. Each table can have multiple unique indexes. Both non-clustered and clustered indexes can be defined as unique.

Code example

create table Person(
  Id int,
  Name varchar(50),
  Gender varchar(1)
);
Insert into Person values(11,'Asma','F');
Insert into Person values(3,'Rameen','F');
Insert into Person values(77,'Ali','M');
Insert into Person values(38,'Wahab','M');
ALTER TABLE Person
ADD Unique KEY (Name);
Select * from Person where Name='Asma';

Explanation

  • Lines 1–10: We create a Person table in SQL and insert records into the table using the Insert statement.
  • Lines 12–13: We add non-unique key constraint on the Name column.
  • Line 15: We use non-clustered index to fetch records based on the Name column.

Note: In this example, when unique key constraint is added, non-clustered index is automatically created.

Free Resources

License: Creative Commons-Attribution NonCommercial-ShareAlike 4.0 (CC-BY-NC-SA 4.0)