Difference between clustered and Non-Clustered Indexes.

 "It is an important question which is asked in an interview as indexing topic."

INTRODUCTION:

In the SQL server, primary key constraint automatically builds the clustered index on the column. There will be only one clustered index per table. It is used to sort the data of the table alphabetically as like dictionary structure. Single table can contain multiple non-clustered indexes. It collects the data and record at one place simultaneously.

CLUSTERED INDEX:

It is the kind of the special type of index which reorders the ways that the records of data of the table are physically stored to match with an index. Whenever the primary key constraint is created on the table, database engine by auto creates the clustered index. View or data are sorted based on the key and values. It stores the data permanently on the disk.

It is created only when both the below conditions are satisfied:

Moved data or file in the secondary memory should be sequential and properly sorted fashion.

Key value should be exist and it ensures that no duplicate or repeated values are allowed.

Composite index is called as the one clustered index which is present on the multiple columns. Insert and update operation is slow down in this index.

NON-CLUSTERED INDEX:

It is the kind of the special type of index in which the logical order of the index does not get match with the physical stored rows on disk. It contains the pointer which points the index no in the memory space.

Index number will be increased each time when new records are inserted in the table. Database engine automatically creates this index, when the table is created with an unique key constraint. It does not store the data permanently on the disk.

It is just like the book index. Book indexes contain chapter name and page number.

 In order to read particular topic or chapter, you need to go through particular chapter name page number. There is no need to go through each and every page number. Data and index are not stored in the same place or location. In other words, they are having another place or location. Since, data and non-clustered index are stored separately, multiple non-clustered index in the table.

DIFFERENCE:

Clustered Index	Non-clustered Index
It is faster than non-clustered index.	It is slower than the clustered index.
Less memory is required to execute the operation.	More memory is required to execute the operation.
Ability to store the data on the disk naturally.	It does not have the data to store on the disk naturally.
Data sheets are stored in the leaf nodes.	Data sheets are not stored in the leaf nodes.
It is the main data.	Index is the main data.
Leaf nodes are itself actual data.	Leaf nodes are not itself actual data. Instead, it contains the multiple columns.
Clustered key will define the order of the data within the table.	Index key will define the order of the table within the index.
By default, primary key of the table is said to be clustered index.	Composite key used with the unique constraint of the table act as the non-clustered index.
It is more prefer for static data environments.	It is more prefer for dynamic data environments.
It contains an index id which is equal to zero.	It contains an index id which is greater than zero.