What is Clustered and Non Clustered in Indexes?

Question

What is Clustered and Non Clustered in Indexes? I need a simple answer? What is the use of clustered and non clustered?

Satyapriya Nayak · Answer

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

Senthilkumar · Answer

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.