Cluster vs Non-Cluster Indexes

Indexes are used to speed-up query processes in a database. They are similar to an index for a book. If you need to go to a particular chapter you can go to the index, find the page number of the chapter and go directly to the page. Database index works the same way. If you create indexes, the database goes to that index first and then retrieves the corresponding table record directly.

There are two types of Indexes in a database:

1. Cluster Index
2. Non-Cluster Index

Clustered Index

Clustered index defines the order in which data is physically stored in a table. There can only be one clustered index per table. In SQL Server, the primary key constraint automatically creates a clustered index. Clustered index maintain the physical order of the stored records according to the the indexed column.

Non-Clustered Index

Non-clustered index are just like clustered index except non-cluster index is stored in a separate place so it takes up more storage space. Clustered index do not consume extra storage.

Table data and non-clustered index data will be stored in different places. The index will be sorted according to its definition and will be sorted separately from the table. You can have multiple non-clustered indexes for a table but only one clustered index. In addition, clustered indexes are faster than non-clustered indexes since they don’t involve any extra lookup step.

Sources:

What is the difference between Clustered and Non-Clustered Indexes in SQL Server?