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

Answer 2

One crude way of thinking about it is to think of a phone book. The clustered index is the order the pages are written in. Any other indexes are separate lists showing which page to go.

For example a phone book is “clustered” on surname but you might also want to lookup by street so you would have a separate list saying people that live on fake street are on pages 3,45 and 63 etc

Answer 3

A unique index is just an index with a unique constraint, nothing more, nothing less. A clustered index orders the data phsyically on the disk to match the index. It is useful if you access the data in the table very often via just one column, e.g. via the primary key. Also a table can have only one clustered index (obvious, of course).

Answer 4

AFAIK every table can have just one clustered index that is the primary key usually, but it may have m any unique indexes.

More: http://decipherinfosys.wordpress.com/2007/07/04/back-to-the-basics-difference-between-primary-key-and-unique-index/