What is difference between “Clustered Index” and “Non Clustered Index”?

Posted by

To view Verified answers click on the button below.

1. A Clustered Index physically stores the data of the table in the order of the keys
values and the data is resorted every time whenever a new value is inserted or a
value is updated in the column on which it is defined, whereas a non-clustered index
creates a separate list of key values (or creates a table of pointers) that points
towards the location of the data in the data pages.

2. A Clustered Index requires no separate storage than the table storage. It forces the
rows to be stored sorted on the index key whereas a non-clustered index requires
separate storage than the table storage to store the index information.
3. A table with a Clustered Index is called a Clustered Table. Its rows are stored in a BTree structure sorted whereas a table without any clustered indexes is called a nonclustered table. Its rows are stored in a heap structure unsorted.
4. The default index is created as part of the primary key column as a Clustered Index.
5. In a Clustered Index, the leaf node contains the actual data whereas in a nonclustered index, the leaf node contains the pointer to the data rows of the table.
6. A Clustered Index always has an Index Id of 1 whereas non-clustered indexes have
Index Ids > 1.
7. A Table can have only 1 Clustered Index whereas prior to SQL Server 2008 only 249
non-clustered indexes can be created. With SQL Server 2008 and above 999 nonclustered indexes can be created.
8. A Primary Key constraint creates a Clustered Index by default whereas A Unique
Key constraint creates a non-clustered index by default.