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:

1. I make A the primary key.
2. 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)

Answer 1

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

Answer 2

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.