20

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.

Improve this question
3
  • 2
    Basically, being clustered and being unique are two distinct and independent characteristics of an index. Commented Jun 10, 2011 at 9:03
  • 1
    @Andriy M - you should post that as an answer. Commented Jun 10, 2011 at 10:50
  • 1
    @Jeff O: Thanks. That wouldn't satisfy the OP's ultimate request, and I couldn't trust myself with a more complete answer. Commented Jun 10, 2011 at 11:39

4 Answers 4

Reset to default
47

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
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

@iMatoria: yes with 2 notes: 1. By default in SQL Server, a PK will be clustered. 2. A PK does not allow NULLs, a "unique clustered" allows one
Considering that, PK doesn't fall in any of the above mentioned 4 permutations? OR is it that there is 2 more permutation: 1. clustured. 2. non-clustured. Sorry, if i am missing something and been already mentioned by you.
@iMatoria: When you create a primary key (CREATE/ALTER TABLE) or an index (CREATE INDEX) you can mention CLUSTERED or NONCLUSTERED (there is no 3rd option). If you don't mention it, it will default to CLUSTERED for a PK, NONCLUSTERED for an index. So basically, yes, a PK is "unique clustered": I was trying to explain why this is so, sorry. Sometimes (not very often) you'll find a NONCLUSTERED PK with some another CLUSTERED index works better for your queries
awesome, thats perfect explanation. Up vote for that. If i had to summarize your previous 2 comments regarding PK, than it would be: PK is "unique clustered" index with extra thing of not allowing NULL values, which is how PK is different from Unique key in simple terms. Thanks anyways.
there is one more difference between PK and UQ i.e you can declare only one PK in a table where as you can declare more than one UQ in a table
|
22

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

Improve this answer

9 Comments

Thanx, what you mean by clustered index sort data physically on the disk?
When you insert data to a table, there is normally no specific order. It just gets inserted somewhere. When you have a clustered index, the data on the disk is ordered by the key you create the clustered index with. e. g. you have a table with an integer field ifield and create a clustered index over it. Then the data on the disk will be ordered ascending by the integer field ifield. First comes record with ifield=1, then record with ifield=2, etc, pp
Thanks once again, but have one more question on this, every table by default has a clustered index when we define any primary key in it? or we have to create it.
No, by default there is no clustered index. Clustered indexes have wide consequences. Every UPDATE, INSERT or DELETE statement could get VERY slow because the DBMS has to re-order the table physically on disk. Only use clustered indexes if you really need them or if you are sure that UPDATE/DELETE/INSERT are very rare.
The pk is by default a Clustered index in SQL server.
|
10

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

Improve this answer

Comments

1

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/

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.