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

itemprop="text">

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

itemprop="text">

class="normal">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%.