DBA Myths: A table with a primary key is not a heap
5May 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?)
SQL Studies 
“heapness”
I think the word you’re looking for is “hepicity”. Or possibly “heapitude” 😉
Love it. Thanks for the comment.
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!
[…] 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 […]