Unique clustered index vs primary key.

Question

Post reply

Unique clustered index vs primary key.

FreeHansje

SSChampion

Points: 11751
More actions
January 24, 2008 at 3:46 am

#119702

Hi there,
Is there a difference between a unique clustered index and a primary key? If not, why would 1 choose the unique clustered index over a primary key?
Greetz,
Hans Brouwer

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

EvilPostIT SSCrazy Points: 2578 More actions · Answer 1

Quotes from book online.

Clustered Index

A clustered index determines the physical order of data in a table.

Primary Key

A primary key constraint ensures no duplicate values are entered in particular columns and that NULL values are not entered in those columns. You can use primary key constraints to enforce uniqueness as well as referential integrity.

Nuke the site from orbit, its the only way to be sure... :w00t:

Paul Mu SSCertifiable Points: 5547 More actions · Answer 2

Also, a UNIQUE CONSTRAINT will allow one NULL value, whereas a PRIMARY KEY cannot accept a NULL value.

Paul

FreeHansje SSChampion Points: 11751 More actions · Answer 3

Tnx for answering.

Evil, I know what a clustered index is, my question was about Unique index(clustered) vs Primary key.

Paul,

The 1 NULL value I had not thought about. Pragmatically however, I cannot imagine that being a consideration on deciding what to choose, unique index or primkey. I admit I myself cannot think of another reason. Maybe there is none, but still I keep wondering...

Tnx again.

Greetz,
Hans Brouwer

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

The point is they serve different purposes, and as a result aren't necessarily the SAME (set of) column(s).

Primary keys are used in setting up relationships. Clustered indexes (and yes - that includes UNIQUE clustered indexes) are used to determine the physical order of storage in a table, and incidentally determine what is referenced in the leaf level of non-clustered indexes.

As a result - the right choice for the clustered index might be different from the Primary key if for example your querying filters heavily against another set of data than the primary key.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?