What is the difference between Cluster and Non-Cluster Index?

[gosai jahnvi]

What is the difference between Cluster and Non-Cluster Index?

[SagarJaybhay]

Both Clustered Index and Nonclustered Indexes have same physical structure and stored as a B-Tree structure. But Single table have single clustered index but more than non clustered index. When you create primary key it will create clustered index by default but you can override this behaviour and if you create unique key it will create non clustered index.

[Rabbit]

That's not quite correct.

A clustered index physically changes the order that the rows are stored on the disk. That is, the order of the rows in the clustered index matches the order of the rows stored on the disk.

A non-clustered index will just have pointers to the rows and so the order of the non-clustered index won't match the order of the rows stored on the disk.

[AjayGohil]

Hi,

Following are difference between Clustered and Nonclustered Index:

• A Clustered Index physically sort all rows while Nonclustered Index doesn't.

• In SQL, one table can only have one Clustered Index but there is no such restriction on NonClustered Index.

• In many relational databases, Clustered Index is automatically created on the primary key column.

• Clustered Index can improve the performance of data retrieval while non-clustered index should be created on columns which are used in the join, where, and order by clause.