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?)

Filed under: Index, Microsoft SQL Server, SQLServerPedia Syndication Tagged: code language, index, language sql, microsoft sql server, T-SQL