Index in SQL Server 2012

Introduction

In this article I describe indexes, types of indexes, creation of indexes and the advantages and disadvantages of indexes.

Index:

Indexes are data structures that are used to improve the searching speed in a table. The user can not see the index directly. An index increases the performance of select statements and where clausees and slows down insert and update statements. So we create indexes only for those columns that are not frequently updated.

Type of index:

1. Implicit Index
2. Explicit Index

Implicit Index:

Implicit Indexes are created automatically, when we apply a Unique Key, Primary Key or other constraint of these types.

Explicit Index:

They are created by the user using the keyword Create Index. Here I describe only an Explicit Index.

First of all we create a table on which we apply the index.

Table:

create table emp(empId int, empName varchar(15))

go

insert into emp

select 1,'d'union all

select 2,'e'union all

select 3,'f'

go

select * from emp

Output:



Creation of index:

create index i_select

on emp(empName) 

 

Creation of composite index.
 

It is created on more than one column of the table using:

create index i_select

on emp(empId,empName) 

Creation of Unique index.

Used for Data Integrity. A Unique index does not allow any duplicate values to be inserted into the table.

create index i_unique

on emp(empId)

How to see the index on the table:

exec sp_help emp

Output:



Deletion of index

drop index i_select on emp

Advantage:

It improve the searching speed.

Disadvantage:

It reduces the insert and update speed.

Summary:

In this article I described indexes in SQL Server. I hope this article has helped you to understand this topic. Please share if you know more about this. Your feedback and constructive contributions are welcome.