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

sql-server indexing clustered-index non-clustered-index
Eric Labashosky picture Eric Labashosky · Sep 18, 2008 · Viewed 285k times · Source

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

Answer

Martynnw picture Martynnw · Sep 18, 2008

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%.

Related questions

What do Clustered and Non clustered index actually mean?

I have a limited exposure to DB and have only used DB as an application programmer. I want to know about Clustered and Non clustered indexes. I googled and what I found was : A clustered index is a special type …

Read More
Difference between clustered and nonclustered index

I need to add proper index to my tables and need some help. I'm confused and need to clarify a few points: Should I use index for non-int columns? Why/why not I've read a lot about clustered and non-clustered …

Read More
SQL Server - When to use Clustered vs non-Clustered Index?

I know primary differences between clustered and non clustered indexes and have an understanding of how they actually work. I understand how clustered and non-clustered indexes improve read performance. But one thing I am not sure is that what would …

Read More