Indexes In SQL Server

Index Overview

An index is created in the table for faster and more efficient retrieval of data. It is being used for optimizing the query performance.

For example, there is a book and there is no Table of Content (TOC) available, in that case, what happens?

You got it right. We need to traverse all the pages of the book to see the particular content. So, TOC helps you as an index, it helps to navigate the content of any page.

Similarly, an Index in SQL server helps to find the specific row or record.

Types of Indexes

There are two types of indexes, clustered index, and non-clustered index.

Clustered index

It’s automatically created when a primary key is assigned, it can also be created manually.

There is only one clustered index allowed per table.

A clustered index is stored as a binary tree (B-tree). Below is the typical structure of B-tree.

In the last leaf node, all the data of the table is stored.

Let’s now see things in action!

Create clustered index automatically

Create a table to see how an automatically clustered index is created when a primary key is assigned

CREATE TABLE [dbo].[Products](
	[product_id] [int] NOT NULL,
	[product_name] [varchar](50) NOT NULL,
	[product_quantity] [int] NOT NULL,
	[unit_price] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[product_id] ASC
)) 
GO

Insert values in the table

insert into Products values(1,'Desktop Computer', 20, 12000)
insert into Products values(2,'Laptop', 25, 18000)
insert into Products values(3,'Tablet', 6, 6000)
insert into Products values(4,'Monitor', 12, 8000)
insert into Products values(5,'Printer', 5, 3000)

Table output

select * from products;

Below screenshot shows, a clustered index that is created automatically when a primary key is assigned during table creation,

When we create a clustered index and give where condition, internally it will use B-tree to traverse faster.

Create clustered index manually

To create clustered index manually, first, we shall drop the table and create the table without assigning a primary key column:

drop table products;

CREATE TABLE [dbo].[Products](
	[product_id] [int] NOT NULL,
	[product_name] [varchar](50) NOT NULL,
	[product_quantity] [int] NOT NULL,
	[unit_price] [int] NOT NULL
) ON [PRIMARY]
GO

Insert values in the table

insert into Products values(1,'Desktop Computer', 20, 12000)
insert into Products values(2,'Laptop', 25, 18000)
insert into Products values(3,'Tablet', 6, 6000)
insert into Products values(4,'Monitor', 12, 8000)
insert into Products values(5,'Printer', 5, 3000)

Using the below query to create clustered index manually

create clustered index productsid_clustered_index 
on products (product_id);

Below is the screenshot where clustered index was created manually

Clustered Index count validation

Below query showing error - cannot create more than one clustered index on table 'products'

create clustered index productsname_clustered_index 
on products (product_name)

The following query is used for dropping the index from the table

drop index products.productsid_clustered_index

Non-Clustered index

It’s created automatically when a unique key constraint is assigned in the column. The maximum number of non-clustered indexes allowed per table is 999

there are two types available when working on non-clustered indexes:

• Unique non-clustered index
• Non unique non-clustered index

Non-clustered index internally uses heap memory where it stores the address of actual data stored so while traversal, the first address from the Heap is found followed by actual data.

Create a unique non-clustered index

The below query is used for creating a unique nonclustered index

create unique nonclustered index productsname_nonclustered_index 
on products (product_name)

Below screenshot showing the unique non-clustered index created

Create a non-unique non-clustered index

The below query is used for creating a non-unique non-clustered index

create nonclustered index productsQuantity_nonclustered_index 
on products (product_quantity)

The below screenshot showing a non-unique non-clustered index created

Conclusion

Indexes are used for query optimization (faster query search). If an index is not applied search result will be very slow because it shall scan all the records.

While indexes are good, over-indexing is not. The index also consumes space so we got to be careful on ‘over indexing’