what is a difference between Clustered Index and Unique Index?

Question

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.

Answer 1

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