Clustered and Non-Clustered Index

Question

What is the difference between a clustered and a non-clustered index?

Nitin · Answer

Here are the main differences between them: One table can only have only one clustered index. One table can only have many non-clustered index. A clustered index requires no separate storage than the table storage. It forces the rows to be stored sorted on the index key. A non-clustered index requires separate storage than the table storage to store the index information. A table with a clustered index is called clustered table. Its rows are stored in a B-Tree structure sorted. A table without any clustered indexes is called non-clustered table. Its rows are stored in heap structure unsorted. The default index created as part of the primary key column is a clustered index.

Prasham Sabadra · Answer

https://msdn.microsoft.com/en-IN/library/ms190457.aspx

Joginder Banger · Answer

Hi pankaj 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 and this is usually made on the primary key. 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. There can be as many as 249 nonclustered index per table