Why Cluster a Primary Key?

[Philip Yale]

I'm probably going to get shot down with thousands of reasons for
this, but I've never really heard or read a convincing explanation, so
here goes ...

Clustered indexes are more efficient at returning large numbers of
records than non-clustered indexes. Agreed? (Assuming the NC index
doesn't cover the query, of course)

Since it's only possible to have one clustered index, why is this
almost always used for the primary key, when by definition a primary
key will always return 1 record?

Isn't it generally better to specify a non-clustered index for the
primary key, and reserve the clustered index for a column which will
most likely be used for queries that return multi-row data sets (e.g.
date columns)?

Also, if you are using a sequential key, clustering this will cause an
insert hotspot on the last page of the table, which can cause
concurrency problems if you aren't using row-level locking. If you're
using a random clustered key then inserts will generally be improved,
assuming you're using a sensible fillfactor, but you still lose the
advantage of using the clustered index for multi-record retrieval.

I'd be very interested to hear other peoples' views on this.

Phil

[Steve Jorgensen]

The main reason I've found for clustering the primary key is that clustering
anything else will mess up front-end libraries including DAO and ADO, and
sometimes clustering the primary key seems to at least keep records together
that were entered close together in time, and those happen to be the ones
close tegether by date which reduces the number of pages hit in date range
queries.

Personally, I almost always have something I'd rather cluster than the primary
key, but with DAO and ADO both assuming the clustered index is the primary key
even when something else actually is, it's just not workable. Either the
clustered index is unique and much larger than the PK leading to unnecessary
network traffic, or the clustered index is not unique, and the front-end
becomes confused that there seems to be more than one record with the same
key.

On 5 Mar 2004 03:56:38 -0800, ph********@btop enworld.com (Philip Yale) wrote:

I'm probably going to get shot down with thousands of reasons for
this, but I've never really heard or read a convincing explanation, so
here goes ...

Clustered indexes are more efficient at returning large numbers of
records than non-clustered indexes. Agreed? (Assuming the NC index
doesn't cover the query, of course)

Since it's only possible to have one clustered index, why is this
almost always used for the primary key, when by definition a primary
key will always return 1 record?

Isn't it generally better to specify a non-clustered index for the
primary key, and reserve the clustered index for a column which will
most likely be used for queries that return multi-row data sets (e.g.
date columns)?

Also, if you are using a sequential key, clustering this will cause an
insert hotspot on the last page of the table, which can cause
concurrency problems if you aren't using row-level locking. If you're
using a random clustered key then inserts will generally be improved,
assuming you're using a sensible fillfactor, but you still lose the
advantage of using the clustered index for multi-record retrieval.

I'd be very interested to hear other peoples' views on this.

Phil

[--CELKO--]

>> Since it's only possible to have one clustered index, why is this
almost always used for the primary key, when by definition a primary
key will always return 1 record [sic]? <<

Actually, you hit the nail on the head and did not know it. When SQL
was first implemented, the mental and physical models for data were
based on files (Rows are not records; fields are not columns; tables
are not files). Files with sequential, contigous storage and in
particular, magnetic tape and punch cards (there is no sequential
access or ordering in an RDBMS, so "first", "next" and "last" are
totally meaningless).

A Master mag tape file is sorted on a key, usually at the front of the
records, just after the "deleted" flag. This is so that you can merge
the transaction tapes, also sorted on the same key, into the Master.

Dr. Codd also fell for this and began with the PRIMARY KEY in first
papers on the relational. A bit later, he caught the error and
realized that a relational key is a key is a key and none of them are
"more equal" than the others. Unfortunately, SQL was based on Codd's
first papers and carried the error forward.

Sybase simply used what was there in Unix and the existing file
systems to build SQL Server and Microsoft followed suit.

Are you familiar with the story of how the Roman Empire determined the
size of the Space Shuttle boosters and therefore most of the design of
the shuttle?

[Philip Yale]

jo*******@north face.edu (--CELKO--) wrote in message news:<a2******* *************** ****@posting.go ogle.com>...

Since it's only possible to have one clustered index, why is this

almost always used for the primary key, when by definition a primary
key will always return 1 record [sic]? <<

Actually, you hit the nail on the head and did not know it. When SQL
was first implemented, the mental and physical models for data were
based on files (Rows are not records; fields are not columns; tables
are not files). Files with sequential, contigous storage and in
particular, magnetic tape and punch cards (there is no sequential
access or ordering in an RDBMS, so "first", "next" and "last" are
totally meaningless).

A Master mag tape file is sorted on a key, usually at the front of the
records, just after the "deleted" flag. This is so that you can merge
the transaction tapes, also sorted on the same key, into the Master.

Dr. Codd also fell for this and began with the PRIMARY KEY in first
papers on the relational. A bit later, he caught the error and
realized that a relational key is a key is a key and none of them are
"more equal" than the others. Unfortunately, SQL was based on Codd's
first papers and carried the error forward.

Sybase simply used what was there in Unix and the existing file
systems to build SQL Server and Microsoft followed suit.

Are you familiar with the story of how the Roman Empire determined the
size of the Space Shuttle boosters and therefore most of the design of
the shuttle?

Thanks for that, Celko. It's very interesting, although I must
confess that I'm not sure what it's got to do with my original
question? Whatever the background evolution of RDBMS systems, in the
real world today what people refer to as a "primary key" returns 1
row, and I feel that it's a bit of a waste putting a clustered index
on this.

BTW - I've heard the Roman theory many times, but this really is just
an urban myth. Railway tracks, for example, in the UK, have a gauge
of 4' 8.5" because this was what resulted from a standard axle width
of 5'. There are many other gauges throughout the world, and there's
a very good paper at
http://www.vwl.uni-muenchen.de/ls_komlos/northam.pdf which details
their evolution.

[Philip Yale]

jo*******@north face.edu (--CELKO--) wrote in message news:<a2******* *************** ****@posting.go ogle.com>...

Since it's only possible to have one clustered index, why is this

almost always used for the primary key, when by definition a primary
key will always return 1 record [sic]? <<

Actually, you hit the nail on the head and did not know it. When SQL
was first implemented, the mental and physical models for data were
based on files (Rows are not records; fields are not columns; tables
are not files). Files with sequential, contigous storage and in
particular, magnetic tape and punch cards (there is no sequential
access or ordering in an RDBMS, so "first", "next" and "last" are
totally meaningless).

A Master mag tape file is sorted on a key, usually at the front of the
records, just after the "deleted" flag. This is so that you can merge
the transaction tapes, also sorted on the same key, into the Master.

Dr. Codd also fell for this and began with the PRIMARY KEY in first
papers on the relational. A bit later, he caught the error and
realized that a relational key is a key is a key and none of them are
"more equal" than the others. Unfortunately, SQL was based on Codd's
first papers and carried the error forward.

Sybase simply used what was there in Unix and the existing file
systems to build SQL Server and Microsoft followed suit.

Are you familiar with the story of how the Roman Empire determined the
size of the Space Shuttle boosters and therefore most of the design of
the shuttle?

Sorry, Joe - didn't mean to call you "Celko" in the previous reply; I
instinctively used your sign-on name!

[Dan Guzman]

> what people refer to as a "primary key" returns 1

row, and I feel that it's a bit of a waste putting a clustered index
on this.
Consider the "Orders" and "Order Details" tables in the sample Northwind
database. The primary keys are respectively OrderID and OrderID/ProductID.
Assuming these tables are frequently joined on OrderID, the Order Details
clustered primary key index reduces i/o and enhances join performance of
these queries.

Of course, there my be a better choice than the primary key for the
clustered index . It all depends on how the data are normally accessed and
there are often trade-offs involved.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Philip Yale" <ph********@bto penworld.com> wrote in message
news:e9******** *************** **@posting.goog le.com... jo*******@north face.edu (--CELKO--) wrote in message

news:<a2******* *************** ****@posting.go ogle.com>...

> Since it's only possible to have one clustered index, why is this

almost always used for the primary key, when by definition a primary
key will always return 1 record [sic]? <<

Actually, you hit the nail on the head and did not know it. When SQL
was first implemented, the mental and physical models for data were
based on files (Rows are not records; fields are not columns; tables
are not files). Files with sequential, contigous storage and in
particular, magnetic tape and punch cards (there is no sequential
access or ordering in an RDBMS, so "first", "next" and "last" are
totally meaningless).

A Master mag tape file is sorted on a key, usually at the front of the
records, just after the "deleted" flag. This is so that you can merge
the transaction tapes, also sorted on the same key, into the Master.

Dr. Codd also fell for this and began with the PRIMARY KEY in first
papers on the relational. A bit later, he caught the error and
realized that a relational key is a key is a key and none of them are
"more equal" than the others. Unfortunately, SQL was based on Codd's
first papers and carried the error forward.

Sybase simply used what was there in Unix and the existing file
systems to build SQL Server and Microsoft followed suit.

Are you familiar with the story of how the Roman Empire determined the
size of the Space Shuttle boosters and therefore most of the design of
the shuttle?

Thanks for that, Celko. It's very interesting, although I must
confess that I'm not sure what it's got to do with my original
question? Whatever the background evolution of RDBMS systems, in the
real world today what people refer to as a "primary key" returns 1
row, and I feel that it's a bit of a waste putting a clustered index
on this.

BTW - I've heard the Roman theory many times, but this really is just
an urban myth. Railway tracks, for example, in the UK, have a gauge
of 4' 8.5" because this was what resulted from a standard axle width
of 5'. There are many other gauges throughout the world, and there's
a very good paper at
http://www.vwl.uni-muenchen.de/ls_komlos/northam.pdf which details
their evolution.

[Joe Celko]

>> .. what people refer to as a "primary key" returns 1
row, and I feel that it's a bit of a waste putting a clustered index on
this. <<

I agree. But this was the default action in the original Sybase product
for the reasons I mentioned and it was carried forward. Programmers are
lazy and don't think; you learn by copying from old code.

Why is "i" used for a loop control variable in procedural langauges?
Because in FORTRAN II, integers began with the letters I thru N.
Wouldn't it be better to come up with a meaning name for the control
within the context of the loop? Sure!

Thanks for the railroad link! Another urban myth bites the dust! Want
to hear the ham bone parable instead :)?

--CELKO--
=============== ============
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Joe Celko]

>> Sorry, Joe - didn't mean to call you "Celko" in the previous reply; I
instinctively used your sign-on name! <<

That is what I go by; even my wife calls me "Celko" and my column in
INTELLIGENT ENTERPRISE is called "CELKO". My family was military and I
grew up in an environment where you used the last name. And thanks for
the link!

--CELKO--
=============== ============
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Philip Yale]

"Dan Guzman" <da*******@nosp am-earthlink.net> wrote in message news:<%i******* ***********@new sread1.news.pas .earthlink.net> ...

what people refer to as a "primary key" returns 1
row, and I feel that it's a bit of a waste putting a clustered index
on this.

Consider the "Orders" and "Order Details" tables in the sample Northwind
database. The primary keys are respectively OrderID and OrderID/ProductID.
Assuming these tables are frequently joined on OrderID, the Order Details
clustered primary key index reduces i/o and enhances join performance of
these queries.

Of course, there my be a better choice than the primary key for the
clustered index . It all depends on how the data are normally accessed and
there are often trade-offs involved.

--
Hope this helps.

Dan Guzman
SQL Server MVP

Thanks Dan.

I quite agree that there are occasions where a clustered primary key
is desirable, and that this is a decision which a DBA should take when
designing the physical database based on the data distribution and
access methods. My contention, though, is that this is often the
exception rather than the rule, contrary to the *default* action taken
when defining a primary key constraint or using a database design
package, both of which tend to assume that all primary keys will be
clustered.

[Daniel Morgan]

Philip Yale wrote:

My contention, though, is that this is often the
exception rather than the rule, contrary to the *default* action taken
when defining a primary key constraint or using a database design
package, both of which tend to assume that all primary keys will be
clustered.

It wouldn't be if more people paid attention to relational database
theory and Joe Celko rather than have a knee-jerk reaction that every
table needs a surrogate key.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.wash ington.edu
(replace 'x' with a 'u' to reply)