sql server - What are the differences between a clustered and a non-clustered index?

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

Answer

Clustered Index

• Only one per table


• Faster to read than non clustered as data is physically stored in index order

Non Clustered Index

• Can be used many times per table


• Quicker for insert and update operations than a clustered index

Both types of index will improve performance when select data with fields that use the index but will slow down update and insert operations.

Because of the slower insert and update clustered indexes should be set on a field that is normally incremental ie Id or Timestamp.

SQL Server will normally only use an index if its selectivity is above 95%.