Xpode.com        Click here to Print this article.

Difference between Clustered & Non Clustered Index

There are clustered and nonclustered indexes. 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.
    1. Can be used for frequently used queries.
2. Can be used in range queries.
    3. Can be used for large result sets.
Clustered indexes are not a good choice for the following attributes:
    1. Columns that undergo frequent changes
    2. Wide keys (Wide keys are a composite of several columns)

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 nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.
1. Queries that do not return large result sets.
2. Contain columns frequently involved in search conditions of a query, such as WHERE clause, that return exact matches
3. Create multiple nonclustered indexes on columns involved in join and grouping operations, and a clustered index on any foreign key columns.
4. The maximum number of non clustered indexes that can be created per table is 999

Contributed by:
Guest user is that user who have done some activity on website without login. Activity contains commenting on any article, uploading an image on website etc.

Resourse address on xpode.com

Click here to go on website