Index in SQL Server 2012

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:

Implicit Index

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:

createtableemp(empIdint,empNamevarchar(15))

go

insertintoemp

select 1,'d'unionall

select 2,'e'unionall

select 3,'f'

go

select*fromemp

Output:

Creation of index:

createindexi_select

onemp(empName)

Creation of composite index.

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

createindexi_select

onemp(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.

createindexi_unique

onemp(empId)

How to see the index on the table:

execsp_helpempOutput:

Deletion of index

dropindexi_selectonemp

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.