Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

SQL Server Indexes Tutorial | Different Types of Indexes in SQL Server | Difference between Clustered Indexes and Non-Clustered Indexes in SQL Server

Jul 30, 2011
Introduction:

Here I will explain about SQL Indexes and different types of indexes and advantages of indexes in SQL Server.

Description:

An index can be created in a table to increase the performance of application and we can get the data more quickly and efficiently. Let’s see an example to illustrate this point suppose now we are reading book in that I need to check the information for dbmanagement to get this information I need to search each page of the book because I don’t know in which page that word information exists it’s time taken process. Instead of reading the each page of book to get that particular word information if I check the index of book (Glossary) it is much quicker for us to get the pages which contains the information with dbmanagement word. By using second method we can save lot of time and we can get information in efficient way.


This same principle applies for retrieving data from a database table. Without an SQL Index, the database system reads through the entire table to locate the desired information. If we set the proper index in place, the database system first go through the index to find out where to retrieve the data, and then go to that location directly to get the needed data. This is much faster due to the SQL Index. Creating and removing indexes on table will not show any effect on application because indexes operate behind the scenes.

Syntax to Create SQL Index in table:

CREATE INDEX INDEX_NAME ON TABLE_NAME (COLUMN_NAME)
Example to create Index on table

CREATE INDEX SampleIndex ON UserInformation (UserName) 
The above statement is used to create an index named “SampleIndex” on the “UserName” column in the “UserInformation” table

If you want to create an index on a combination of columns, you can list the column names within the parentheses, separated by commas:

Example of creating SQL Index on multiple columns

CREATE INDEX SampleIndex ON UserInformation (UserName,FirstName) 

To Drop Index on table use the below statement

DROP INDEX TABLE_NAME.INDEX_NAME
In SQL we are having two types of indexes are there

1)    Clustered Index
2)    Non-Clustered Index
 
Clustered Index

Only 1 allowed per table physically rearranges the data in the table to confirm to the index constraints for use on columns that are frequently searched for ranges of data for use on columns with low selectivity.

Non-Clustered Index

Up to 249 allowed per table creates a separate list of key values with pointers to the location of the data in the data pages For use on columns that are searched for single values For use on columns with high selectivity

A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages. A non-clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non-clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

 

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

19 comments :

Anonymous said...

nice post

Niks said...

nice job sir..

sunil said...

I have a doubt i want to display the information in div tags from database how is it possible can you show some way

Venkatesh said...

Its very useful.

Anonymous said...

One of the Best asp.net resource on The Web

Anonymous said...

thanks dude.........!

Anonymous said...

Really Nice website.Great job.Very useful.Thank you!!

savitha said...

Really good explanation...:-)

velu said...

thanks a lot for Ex. of "Reading the book".

Anonymous said...

select * from table_name with(index(index_name))

Ghanshyam Naga said...

select * from table_name with(index(index_name))

you can retrieve the sorted data from table

Anonymous said...

good,,,One of the Best asp.net resource on The Web

Suneel Kumar Biyyapu said...

it is well understandable if you will be given Examples

Anonymous said...

Please give the examples of Clustered Index and Non-Clustered Index,which will help us to understand the concept in detail.

Anonymous said...

Very nice explanation

Unknown said...

It is very easy way to understand :-)

Unknown said...

i want to know that how many types of indexs

Unknown said...

if there are 2 types cluster,non-cluster than what are b-tree,bitmap.function please define them all with defination

Ayur Manthra(Ayurvedic Health Tips) said...
This comment has been removed by the author.

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.