Indexes In SQL With Example

Introduction

In this post we will learn what is Index in SQL server. Why do we need indexer in sql ,how to use indexes and types of Indexer in SQL.

Previous Updates
In previous articles we have what is  Sequence in sql server  Pivot table in SQl. Difference between  Scope_Identity(), @@Identity and Ident_Current . Stuff and Replace in SQl. Temp table and table variable .Group By in SQL Server and its use. If Else and Case statement  , While Loop . What is Cursor in sql and use of cursor. Difference between Row_Number(), Rank(), Rank_Density() with example.

What is Index

Index is used to increase the performance of searching result in database or retrieves the data very fast from database.

In daily life example suppose we have a book having 1000+ pages which have many sections and chapters and no table of content available in front page of the book .
So what happend when you want to locate a specific section of a chapter. It will be a time consuming process for you and you will go through all the book until you not find the chapter.  
So if we have table of content or Indexing in start of that book we can easily find chapter page wise and we will directly go to that page in very less time . Same thing happens with Sql which is sort out by Indexers.

In SQL server scenerio by using index you can be easily and relevantly find your data .for example if you have a index on your table on your PrimaryKey column and when you perform any SELECT operation on that table then SQL first goes to Index to find the matched record instead of doing whole table scan.

If you have large data on your table and you are not using indexes on that table then it will be a bulky process for sql server to processed these table records quickly.

Index is always unique means you can not have duplicate entries in that column. Index speed up the Select and Where clause in SQL server but it slows down while performing the Update and the Insert operation.

Create Index In SQL

CREATE INDEX index_name ON table_name;

DROP Index In SQL

DROP INDEX index_name 

Types Of Index 

As per Microsoft SQL have 8 Indexes which are following.

• Clustered Index
• NonClustered 
• Unique
• Index with included columns
• Full-Text
• Spatial
• Filtered
• XML

We mainly use two main Indexes all the time clustered and nonclustered. Brief description given below for these two.

Clustered Index

Clustered index creates a physical order of row. Means it determines the order of data in a table. 

A Table has only one Clustered Index. Because Clustered index responsible for physical order of row that's why we did not make any other clustered index in a single table.

Ex- if you have a table having Primary-Key column then sql server automatically creates a clustered index on that column . And this column always take care of table indexing.

Suppose we insert few record in our table example given below-

INSERT INTO TestTable Values (1, 'Mark','California') INSERT INTO TestTable Values (4, 'JP',' Vegus') INSERT INTO TestTable Values (2, 'Nick','Vegus') INSERT INTO TestTable Values (3, 'Shiva','UAE')

After inserting Id in different series write a select query to check table data then the output looks like this-

Here your clustered index automatically manage the ID column in ascending order.  And we did not specify this type of indexing in more then one column, that's the reason sql only support single clustered index in a table. But you can use more then one columns in a single clustered index.

NonClustered Index

It does not create a physical order of row. In fact you can create a number of non clustered index in a single table.

It makes our searching fast but it is slower from Clustered index. Because clustered index is with in table and sql did not do looping for searching

We will understand how indexing works and difference between clustered and non clustered indexes in my next update. Hope this post will helpful for you.