DBA Myths: A table with a primary key is not a heap

5

May 21, 2014 by Kenneth Fisher

Typically when you see a heading like this you know the answer is “No” or “False” but in this case it’s more of a “hu?” You see a primary key and a table being a heap have nothing to do with each other. Well very little anyway.

  • A “Primary Key” is a special case of a unique constraint (enforced by an index) that will not allow NULL values. There can be only one Primary Key.
  • A “Heap” on the other hand is a table without a clustered index.

 
Note the important terms here are “unique key” and “clustered index”. I should probably point out that a unique key/primary key can be clustered or non-clustered. Thus a table with a primary key can be a heap or not.

So why the confusion? Usually the default when you create a primary key is to create a unique clustered index to enforce it. Thus by default creating a primary key does in fact stop a table from being a heap. However you can override this (and in some cases clustered isn’t even the default) and create a non-clustered unique index to support the constraint. And a non-clustered primary key does not affect a table’s “heapness” (is that even a word?)

5 thoughts on “DBA Myths: A table with a primary key is not a heap

  1. seb says:

    “heapness”

    I think the word you’re looking for is “hepicity”. Or possibly “heapitude” 😉

  2. Joe Celko says:

    Wait until you work with other SQLs. Teradata and larger products use perfect hashing to enforce uniqueness because they have no indexes.

    • Fair enough. The only other DBMS I work with these days is DB2 and I don’t even know it well enough to be certain how it handles uniqueness. I should probably make it more clear that I’m talking specifically about MSSQL.

      Thanks for the comment!

  3. […] want to point out a few things about my tests. I’m using code to create/modify a PRIMARY KEY. PRIMARY KEYs are not necessarily backed by CLUSTERED INDEXes but they are by default and that’s what I’m using here. I could just as easily have […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,614 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: