Clustered/non-Clustered Index

What does an index add to the performance?

Why do we use Clustered Index and Non-clustered Index?

thanks

Indexes are used to improve the performance of queries

A clustered index will have more performance while executing select query

but take more overhead for insert,delete statements where as a non clustured index will not have any overhead on insert and delete but does not give as much performance hit as of a clustred index for more information on indexed look the following link

An Introduction to Clustered and Non-Clustered Index Data Structures

|||

It's a long story.

Shortly, indexes shorten the time to look for data (when established properly). The 10 000 feet explanation is that clustered index orders rows physically, and data is stored on leaf node of a index, while non-clustered index only points to data location (bookmark: heap or underlying clustered index). Indexes are stored as B-tree (balanced tree) where node levels of each type contain pointers to pages at the next level, while the leaf level contains the key values

Without index, SQL Server has to do table scan and other tricks to locate data, e.g look through the entire table.

You'll find docs here:

Table And Index Architecture
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_8sit.asp

Clustered Indexes
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_1tbn.asp

Non-clustered indexes
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_75mb.asp

|||

In SQL Server you can create one clustered index and 249 none clustered indexes per table, all none clustered indexes include your clustered index. I don't think you can create all 249 none clustered indexes because it is better to let the index tuning wizard which is part of the SQL Server profiler to guide you in your index creation. To improve performance look up index covering in the BOL(books online). Hope this helps.