#SQL What is the difference between a Clustered and Non Clustered Index?

What is the difference between a Clustered and Non Clustered Index?

1.       One table can only have only one clustered index.
2.       One table can only have many non-clustered indexes.
3.       A clustered index requires no separate storage than the table storage. It forces the rows to be stored sorted on the index key.
4.      A non-clustered index requires separate storage than the table storage to store the index information.
5.      A table with a clustered index is called clustered table. Its rows are stored in a B-Tree structure sorted.
6.      A table without any clustered indexes is called non-clustered table. Its rows are stored in heap structure unsorted.
7.     The default index created as part of the primary key column is a clustered index
8.     The default index created as part of the unique key column is a non-clustered index.
9.       Prior to SQL Server 2008 only 249 Non-clustered Indexes can be created. With SQL Server 2008 and above 999 Non-clustered Indexes can be created

A table can have multiple non-clustered indexes

A table can have multiple non-clustered indexes because they don’t affect the order in which the rows are stored on disk like clustered indexes.

Why can a table have only one clustered index?

Because a clustered index determines the order in which the rows will be stored on disk, having more than one clustered index on one table is impossible. Imagine if we have two clustered indexes on a single table – which index would determine the order in which the rows will be stored? Since the rows of a table can only be sorted to follow just one index, having more than one clustered index is not allowed.