Diff in PRIMARY KEY and CLUSTERED INDEX

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Diff in PRIMARY KEY and CLUSTERED INDEX

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2006-03-27 : 10:43:00

Any insight in the difference in PRIMARY KEY and UNIQUE CLUSTERED INDEX?

X002548
Not Just a Number

15586 Posts

Posted - 2006-03-27 : 11:28:13

Sam, is it that time of year already?

kaYAKing down the river? Did you hit your head?

You want to cluster the data in a manner that makes for the fastest access. Usually the PK and cluster are the same, however it does not always need to be the case.

The surrogate key fan club probably have the most instance where the PK is not the cluster.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-27 : 11:36:36

If you take out the clustered from your unique clustered index, a unique index will provide similar functionality to a primary key in that they both will enforce uniqueness and be available as foreign keys for RI. You can have well indexed tables with carefull consideration to which key(s) are clustered as well as appropriate referencial integrety established all without having a primary key defined.

related note:
Your clustered index key will be the row identifier for all other indexes on the table so a wide clustered index will increase the size of all other indexes on the table.

Be One with the Optimizer
TG

SamC
White Water Yakist

3467 Posts

Posted - 2006-03-27 : 11:47:24

So... Primary Keys are clustered... right?

X002548
Not Just a Number

15586 Posts

Posted - 2006-03-27 : 11:53:46

No, not always.

For examle, let's say a big surrogate key fan has a STATE_CD table.

They make the primary ket an IDENTITY Column (no don't aks why, but these people do).

I would cluster my index on the State_CD Column, not the identity column.

There are probably better real world examples, I just can't think of one right now.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

nr
SQLTeam MVY

12543 Posts

Posted - 2006-03-27 : 12:01:44

A primary key is unique and all columns must be not nullable.
It can be clustered or non-clustered - it defaults to clustered if there isn't another clustered index on the table (for some reason).

A clustered index can be a primary key, unique index or non unique.

A primary key will be supported by a unique index.

The PK is used by some things like transactional replication is it is meant to be row defining (hence columns in a PK should never be updated imho).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SamC
White Water Yakist

3467 Posts

Posted - 2006-03-27 : 14:53:29

quote:
Originally posted by nr

A primary key is unique and all columns must be not nullable.
It can be clustered or non-clustered - it defaults to clustered if there isn't another clustered index on the table (for some reason).

A clustered index can be a primary key, unique index or non unique.

A primary key will be supported by a unique index.

This, and Brett's comment seem to fill in the gaps. Primary keys are implicitly CLUSTERED unless some other index is explicitly specified as CLUSTERED.

What remains is why an index would be referred to as "PRIMARY" if some other index is clustered. If it isn't CLUSTERED, how can it be PRIMARY in any way?

Seems oxy-moronic, like "High Speed Buss" or "Government Efficency Expert".

nr
SQLTeam MVY

12543 Posts

Posted - 2006-03-27 : 15:39:57

The PK is meant to be row defining - it's nothing to do with being clustered.

When you decide on an entity you have a number of candidate keys which are unique. One of these becomes the primary key and the others alternate keys. The only thing special about the PK is that it identifies the row. Clustering is an implemantational feature wheras the PK is part of the design.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-27 : 15:46:05

quote:

Primary keys are implicitly CLUSTERED unless some other index is explicitly specified as CLUSTERED.

No. Clustered is the default for a primary key, so you don't have to specify if you want it to be clustered. If some other column is specified as clustered, then you can't have other clustered indexes.

PRIMARY has to do with a filegroup and not with a primary key. It's just where the data is going to reside.

Tara Kizer
aka tduggan

SamC
White Water Yakist

3467 Posts

Posted - 2006-03-27 : 15:54:03

quote:
Originally posted by nr

The PK is meant to be row defining - it's nothing to do with being clustered.

When you decide on an entity you have a number of candidate keys which are unique. One of these becomes the primary key and the others alternate keys. The only thing special about the PK is that it identifies the row. Clustering is an implemantational feature wheras the PK is part of the design.

Not to pick

..., let's take this example another step:

Given some index other than the PRIMARY KEY index in a table is clustered, the clustered index makes claim to benefit directly from the index defining the physical order in the table (compared to non-clustered indexes).

NON-clustered indexes cannot benefit (directly) from physical ordering of table index data. Given that several non-clustered indexes may exist along with the non-clustered "PRIMARY KEY", is there any attribute of the PRIMARY KEY index which would distinguish it from the other non clustered indexes on that table?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-27 : 15:56:46

A primary key is a constraint, not an index. It is supported by a unique index to guarantee uniqueness.

Tara Kizer
aka tduggan

SamC
White Water Yakist

3467 Posts

Posted - 2006-03-27 : 16:06:09

quote:
Originally posted by tkizer

A primary key is a constraint, not an index. It is supported by a unique index to guarantee uniqueness.

Sure, but what I was trying to get to the bottom of was: what makes "PRIMARY KEY" special from other indexes?

From what I've gathered in this thread, "PRIMARY KEY" is shorthand for: UNIQUE NOT NULL [CLUSTERED -- if there is no other explicitly declared CLUSTERED index]

The PRIMARY KEY designation doesn't seem to have any other attribute that distinguishes it from other table indexes. ??

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-27 : 16:16:24

You can't compare apples and oranges. A constraint is different from an index. Now if you want to compare a unique index with other indexes, then that would be apples and apples.

Primary key is not shorthand for UNIQUE NOT NULL... It will have those attributes but so can other unique constraints.

And the "if there is no other explicitly declared clustered index" part of your reply is incorrect as well. An index is either clustered or non-clustered. If you create an index and don't specify it in the create statement, then it will default to non-clustered. The opposite is true of a primary key. Default is clustered for primary key. A clustered index only exists if one was created not because no other index is not declared clustered. So whether or not it is clustered or not is based upon its definition.

Tara Kizer
aka tduggan

SamC
White Water Yakist

3467 Posts

Posted - 2006-03-27 : 16:30:38

quote:
Originally posted by nr

A primary key is unique and all columns must be not nullable.
It can be clustered or non-clustered - it defaults to clustered if there isn't another clustered index on the table (for some reason).

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-27 : 16:34:30

Everything that he said is true. The clustered, non-clustered bit is referring to the index that comes with the constraint. The key to understanding this is to understand that the primary key is a constraint and is supported by a unique index. The default for the index for a primary key is clustered. The opposite is true of a regular index.

Tara Kizer
aka tduggan

jhermiz

3564 Posts

Posted - 2006-03-27 : 16:59:39

Primary key must contain data; not nullable. Unique clustered index can contain one null no ?

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-27 : 17:08:26

Yes that is true Jon.

Tara Kizer
aka tduggan

jhermiz

3564 Posts

Posted - 2006-03-27 : 17:27:41

quote:
Originally posted by tkizer

Yes that is true Jon.

Tara Kizer
aka tduggan

There you go SamC the only thing you need to worry about

.
One contains data the other doesn't

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

SamC
White Water Yakist

3467 Posts

Posted - 2006-03-27 : 18:09:49

UNIQUE is a constraint as is PRIMARY KEY. All the posts in this thread show PRIMARY KEY implies: UNIQUE NOT NULL. Both the PRIMARY KEY and UNIQUE constraints will create a index, however, UNIQUE alone creates a nonclustered index, while PRIMARY KEY will create a CLUSTERED index, unless some other index on the table is explicitly declared CLUSTERED.


CREATE TABLE dbo.MyTable1 (
  Mycol INT NOT NULL ,
  PRIMARY KEY  CLUSTERED (Mycol) 
)

-- is functionally the same as

CREATE TABLE dbo.MyTable2 (
  Mycol INT NOT NULL ,
  UNIQUE CLUSTERED (Mycol)
)

Except the type of constraint is PRIMARY KEY in the first and UNIQUE in the second.

Is there any difference in PRIMARY KEY CLUSTERED and UNIQUE CLUSTERED??

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-27 : 18:23:22

Primary keys do not allow nulls, unique does (well one that is). This is not related to clustered or non-clustered.

Tara Kizer
aka tduggan

jhermiz

3564 Posts

Posted - 2006-03-27 : 20:31:03

quote:
Originally posted by SamC

UNIQUE is a constraint as is PRIMARY KEY. All the posts in this thread show PRIMARY KEY implies: UNIQUE NOT NULL. Both the PRIMARY KEY and UNIQUE constraints will create a index, however, UNIQUE alone creates a nonclustered index, while PRIMARY KEY will create a CLUSTERED index, unless some other index on the table is explicitly declared CLUSTERED.

CREATE TABLE dbo.MyTable1 (
Mycol INT NOT NULL ,
PRIMARY KEY CLUSTERED (Mycol)
)

-- is functionally the same as

CREATE TABLE dbo.MyTable2 (
Mycol INT NOT NULL ,
UNIQUE CLUSTERED (Mycol)
)
Except the type of constraint is PRIMARY KEY in the first and UNIQUE in the second.

Is there any difference in PRIMARY KEY CLUSTERED and UNIQUE CLUSTERED??

See my reply above: "Primary key must contain data; not nullable. Unique clustered index can contain one null no ?"

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources