SQL Server Index

Introduction

Every day we need to work with a huge amount of information. People are doing composite work. In order to work properly 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

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

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

Types of Index

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

1. Clustered
2. Non-Clustered

Clustered Index

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.

Fox example, I am creating an Employee table.

CREATE Table Employee (

EmpID int identity (1,1),

EmpName varchar(35),

Cell varchar(20),

DeptID int

);

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.

Non-Clustered Index

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

If there are no Indexes then the table is considered as 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, they do not need to be sorted. Whereas, 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.