What are the differences between a clustered and a non-clustered index

Introduction

Hope you liked my previous article on Understanding The Relationships in SQL Server. In this article, I am going to discuss clustered and non-clustered indexes in SQL. An index is a disk structure related with a table or view that accelerates the extraction of rows from the table or view. It contains keys created from one or more columns in the table or view. These keys are stored in a B-tree structure that allows SQL Server to rapidly and proficiently discover the rows associated with the key values.

SQL supports two types of indexes:

• Clustered index
• Non-clustered index

Clustered index

Clustered indexes sorts and stores data in table or view based on their key values. There is only one clustered index per table and this table with clustered index is known as clustered table.

Non-Clustered index

Non-clustered indexes have a separate structure from data rows. A non-clustered index comprises of non-clustered index key values. Each one of these key value entry has a pointer to the data rows that includes the key value. It is important for columns that have repeated values.

Difference between Clustered and Non-clustered index in SQL

The difference between clustered and non-clustered indexes is one of the most asked questions during interviews.
Given below are some of the differences between clustered and non-clustered indexes:

1. Clustered index sorts data rows whereas non-clustered doesn't.
2. One table can have one clustered index, but there is no such condition on non-clustered index.
3. In many databases, a clustered index is directly made on primary key.
4. Clustered Index is more faster to non clustered index because non clustered  physically stored in index order. 
5. Non clustered index is more faster for insert and update rather than clustered index.
6. Clustered index is stored logical so it don't require extra storage.

Conclusion

That’s all on difference between clustered and non-clustered indexes in SQL. Both indexes are extremely critical in SQL.

Relevant Reading

• Saving Changes not permitted in SQL Server
• Understanding The Relationships in SQL Server
• How to avoid duplicate records in SQL view using Entity Framework