Difference between clustered index and non-clustered index in SQL server

An Index is a lookup table associated with an actual table or View that is used by the database to improve the data retrieval performance timing. In an index, keys are stored in a structure (B-tree) that enables SQL Server to find the rows associated with the key values quickly and efficiently. Index gets created automatically if the primary key and unique key constraints are defined on the table. There are two types of index namely, Clustered Index and Non-Clustered Index.

Read this article to learn more about clustered index and non-clustered index and how they are different from each other.

What is a Clustered Index?

A Clustered index is a type of index where the table records are physically reordered to match with the index. If a Table is created with primary key constraints, then the database engine automatically creates a clustered index. In this, data is sorted or stored in the table or view based on their key and values.

A clustered index contains the main data. It is quick, thus the data retrieval is faster in the clustered index, and it also requires less memory space to perform operations. A clustered index has the inherent ability to store data on the disc.

What is Non-Clustered Index?

A Non-clustered index is a special type of index in which the logical order of the index does not match with the physically stored order of the rows on the disk. In this type of index, the table is created with UNIQUE constraints, then the database engine automatically creates a non-clustered index.

A non-clustered index contains the non-clustered index keyvalues and each keyvalue entry has a pointer to the data row that contains the key value. However, a non-clustered index is relatively slower, and it requires more memory space to perform the operations. Also, a non-clustered index does not have the inherent property of storing data on the disc.

Difference between Clustered Index and Non-Clustered Index

The following table highlights all the major differences between clustered index and non-clustered index in SQL server −

Key	Clustered Index	Non-Clustered Index
Basic	Clustered index is created on primary key.	Non-clustered index can be created on any key.
Ordering	Store data physically according to the order.	It doesn't impact the order.
Number of index	Only one clustered index can be there in a table.	There can be any number of non-clustered indexes in a table.
Space	No extra space is required to store logical structure.	Extra space is required to store logical structure.
Performance	Data retrieval is faster than non-cluster index.	Data update is faster than clustered index.
Leaf node	The leaf nodes contains the actual data in the clustered index.	The leaf nodes does not contain the actual data in the non-clustered index.
Number of indices	A table can have only one clustered index.	A table can have multiple non clustered indices.
Data	Clustered index contains main data.	Non-clustered index contains a copy of data.

Conclusion

The most significant difference that you should note here is that a clustered index can be created on the primary key only, while a non-clustered index can be created on any key.