SQL Server Index

Introduction

People are doing composite work. Every day we need to work with a huge amount of information. To work correctly and efficiently with the huge amount of information, we need a system where we can keep our data and manipulate this data, and we can even share this data all over the world. It is one of the best reasons for using a Database Management System.

Index in SQL Server 

Database tables are not enough for getting the data efficiently in case of a huge amount of data. An Index is one of the most powerful techniques to work with this enormous amount of information. We need to index the column in a table to get the data quickly.

An index is a database object created and maintained by the DBMS. An index can be applied to a column or a view. A table can have more than one Index. Indexed columns are ordered or sorted so that data searching is first.

Types of Index in SQL Server

Microsoft SQL Server has two types of indexes. These are:

1. Clustered
2. Nonclustered

Clustered Index in SQL Server

A Clustered Index sorts and stores the data in the table based on keys. A Clustered Index can be defined only once per table in the SQL Server Database because the data rows can be sorted in only one order. Text, nText, and Image data are not allowed as a Clustered index.

For example, I am creating an Employee table.

CREATE Table Employee (
  EmpID int identity (1, 1), 
  EmpName varchar(35), 
  Cell varchar(20), 
  DeptID int
);

We are now creating a procedure for inserting some temporary records to test it more clearly.

CREATE Procedure InsertIntoEmplyee AS 
SET 
  NOCOUNT ON BEGIN DECLARE @EN varchar(35) = 'Black Smith' DECLARE @Cell varchar(20) = '12345678901' DECLARE @DID int = 1 DECLARE @Count int = 1 WHILE @Count < 200000 BEGIN INSERT INTO Employee(EmpName, Cell, DeptID) 
VALUES 
  (@EN, @Cell, @DID) 
SET 
  @DID += 1 
SET 
  @Count += 1 END END;
EXEC InsertIntoEmplyee;

The following shows how to select rows without Clustering.

SET STATISTICS IO ON

SELECT * FROM Employee WHERE EmpID=20001

EmpID     EmpName             Cell                 Dept

20001     Black Smith       12345678901       1

The following shows how to create a Clustered Index on the EmpID column:

CREATE CLUSTERED INDEX CL_ID ON Employee ( EmpID );

Again selecting the same record. Now you will see the following Clustered Index to reduce logical reads.

SET STATISTICS IO ON

SELECT * FROM Employee WHERE EmpID=20001

EmpID EmpName           Cell              Dept

20001 Black Smith       12345678901       1

Now the logical reads decrease from 1178 to 3.

Nonclustered Index in SQL Server

Nonclustered Indexes, or simply indexes, are created outside of the table. SQL Server supports 999 Non-Clustered per table, and each Nonclustered can have up to 1023 columns. A Nonclustered Index does not support text, nText, or image data types.

If there are no Indexes, the table is considered a heap, and the rows are not sorted in a defined order. This is useful when insert speed is a factor. When a new row is inserted into a heap or table, it does not need to be sorted. At the same time, an indexed insert query needs to be sorted in a specific location to maintain the index sort order.

Before NONCLUSTERED INDEX

SET STATISTICS IO ON

SELECT * FROM Employee WHERE DeptID=20001

EmpID EmpName           Cell                   Dept

40001 Black Smith       12345678901       20001

After NONCLUSTERED INDEX

CREATE NONCLUSTERED INDEX NCL_ID ON Employee ( DeptID )

SET STATISTICS IO ON

SELECT * FROM Employee WHERE DeptID=20001

EmpID EmpName           Cell              Dept

40001 Black Smith       12345678901       20001

Now the logical reads are decreased from 1168 to 5.

Conclusion

This article taught us about indexes and their different types and code examples in SQL Server.