Clustered vs Non-Clustered Index in SQL Server

Clustered Index:

A clustered index in SQL Server dictates the physical order of data rows in a table. It rearranges the table's rows based on the indexed column(s) values. There can be only one clustered index per table, and it is usually the primary key. As the data is physically stored in the order of the clustered index, there can be performance benefits for queries that involve range-based searches or sorting.

Non-Clustered Index:

A non-clustered index does not alter the physical order of data rows in a table. Instead, it creates a separate structure that points to the corresponding data rows based on the indexed column(s) values. Multiple non-clustered indexes can be created on a single table. Non-clustered indexes are useful for improving the performance of queries that search or sort by the indexed column(s) but do not provide the same performance benefits for range-based searches as clustered indexes.

Summary:

In conclusion, the main difference between a clustered and non-clustered index in SQL Server lies in how they determine the physical ordering of data rows. While a clustered index rearranges the rows themselves, a non-clustered index creates a separate structure to point to the rows. Understanding when to use each type of index can greatly impact the performance of your SQL Server queries.