What is Clustered and Non Clustered in Indexes?

Hi MohanRaj,

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 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.


Please refer the below links

http://www.sql-server-performance.com/2004/index-data-structures/

http://www.codeproject.com/Articles/173275/Clustered-and-Non-Clustered-Index-in-SQL-2005


Thanks

Hi,
Clustered index is node will have the actual value and only one clustered index can have a table. When you create the primary key or unique key it will create the clustered index automatically.

It will do the sorting when every you do the DML operation on the table.

Non clustered table is actually, node will have the reference to the actual page of the data. A table can have many non clustered index. Sql server 2005 can have 249 and sql server 2008 can have 999.

Non clustered index will have separate index table, it will search first and map with the actual table, if the value is not there then it will go and search in actual table.

Hope this will help you.