SQL Server: Non-null unique vs. Primary Key

Question

Let's say I have some table T with some non-null field A. There is a clustered index on A. Consider the following two options:

I make A the primary key.
I create a UNIQUE constraint for A.

Where's the difference? I know what a primary key is, and I know that there is a conceptual difference w.r.t. database theory. But what is the actual difference in SQL Server? How does the SQL Server database engine behave differently? In both cases, the clustered index of the table is on A; and in both cases I can refer to A through foreign keys.

(Related question which inspired this question: Meaning of Primary Key to Microsoft SQL Server 2008)

I had this exactly question in 2023! So the keywords PRIMARY KEY in T-SQL are just convenient shorthand for a NOT NULL column with a uniqueness constraint. — Ed Graham
– Ed Graham, Commented Sep 20, 2023 at 9:51

Dave Markle · Accepted Answer · 2010-02-14 23:15:31Z

No. There's really no difference. The optimizer treats it the same way. There's even debate among certain circles about whether or not primary keys, per se, are really necessary. (Though nobody would argue that you should define at least one unique index/constraint per table).

An argument against the "primary key" concept might go like this: If you have two columns on a table, and they're both unique and the same amount of data, which becomes the PK? A fine question indeed.

Anyway, I always use the PRIMARY KEY concept because it's pretty helpful from a documentation perspective, and it really helps folks understand your intent.

zerkms · Accepted Answer · 2010-02-14 22:40:46Z

3

I never get any real difference but conceptual. But i think about this much times too.

answered Feb 14, 2010 at 22:40

zerkms

256k73 gold badges447 silver badges551 bronze badges

Collectives™ on Stack Overflow

SQL Server: Non-null unique vs. Primary Key

2 Answers 2

Comments

Comments

Hot Network Questions