Sql-server – what is a difference between Clustered Index and Unique Index

sql-serversql-server-2005sql-server-2008

I am just learning about Indexing in SQL server but got confuse between Clustered and Unique index. if both are applied on a unique key column ex: PersonID. so what is difference between both.

Thanx.

Best Solution

The two are unrelated:

  • "Unique" ensures each value occurs exactly once only
  • "Clustered" is how the data is arranged on disk

You can have all 4 permutations:

  • "unique non-clustered"
  • "unique clustered"
  • "non-unique non-clustered"
  • "non-unique clustered"

Some confusion occurs because the default for a "primary key" (PK) in SQL Server is clustered.

A "primary key" must always be unique though. The difference between "unique" and "PK" is that unique allows one NULL, PK doesn't allow any NULLs.

Finally, some number limits

  • because clustered refers to the on disk layout, you can have only one clustered index per table
  • a table can't have more than one pimary key but can have many unique indexes
Related Question