Pages

Ads 468x60px

For New Update Use this Link http://dotnethubs.blogspot.in/ Offer for you

.

Showing posts with label What is an index. Show all posts
Showing posts with label What is an index. Show all posts

Monday 17 June 2013

What is index in sql server

what is index in sql server

Indexes: -
Indexes in SQL Server are similar to the indexes in books. They help SQL Server retrieve the data quicker.

Type of Indexs :-
Indexes are of two types.  1-:   Clustered indexes   2-: non-clustered indexes

Clustered indexes :- 

you create a clustered index on a table, all the rows in the table are stored in the order of the clustered index key. So, there can be only one clustered index per table.

Non-clustered indexes :-

Non-clustered indexes have their own storage separate from the table data storage. Non-clustered indexes are stored as B-tree structures (so do clustered indexes), with the leaf level nodes having the index key and it’s row locater. The row located could be the RID or the Clustered index key, depending up on the absence or presence of clustered index on the table.

Advantages of Indexes :-
1-:  If you create an index on each column of a table, it improves the query performance, as the query optimizer can choose from all the existing indexes to come up with an efficient execution plan.

Disadvangages of Indexes :-
1 -: At the same t ime, data modification operations (such as INSERT, UPDATE, DELETE) will become slow, as every time data changes in the table, all the indexes need to be updated.

2-: Another disadvantage is that, indexes need disk space, the more indexes you have, more disk space is used.
 

..




New Updates

Related Posts Plugin for WordPress, Blogger...

Related Result