Saturday, January 22, 2011

Difference between Cluster and Non-cluster index

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.
Clustered index will be created by default when u 
create primary key on a column. So we can create one 
clustered index per table. clustered index is stored in 
serial passion.

A nonclustered 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. A table can have 249 non clustred index.Non clustered index will be created automatically when u 
create unique key on a column. A table can have no.of 
unique keys, so we can create no.of non clustered indexes 
per table.

1 comment:

  1. order of the index does not match the physical
    stored order

    the text "match the physical" seems missing

    ReplyDelete