1

In SQL Server, I have a non nullable column with a unique clustered index on it. If I make this column a Primary Key the exact same index is created automatically plus the column is recognized as a Primary Key.

I understand the abstract/semantic difference.

(Primary Key identifies the entity, while any other column with this index may not.
For example, a Person can have Email field which is Unique,Non-nullable... but can be changed)

But what bothers me is the actual difference when it comes to the DB engine itself.

What will happen if I will just create an Id column, make it non-nullable, create a unique clustered index for it, make it Identity Increment, but without the Primary Key constraint?

In what scenarios the Primary Key constraint comes into play?

(I've looked at many related questions before asking this, but all the answers I saw ended up with an abstract/theoretical explanation).

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
Yaron Levi
  • 12,535
  • 16
  • 69
  • 118

2 Answers2

5

Nothing will be different really. You specify PRIMARY KEY to relay your intentions, not so that the engine does anything differently. When constructing a query plan, the optimizer will still use the uniqueness for all of its properties, and will still use the clustered index for all of its properties, regardless of whether you technically created it as a PRIMARY KEY. When creating a FOREIGN KEY, you can still reference the column(s) specified as unique (clustered or not). The difference is solely in the metadata (sys.indexes.is_primary_key) and in SSMS' representation to you (oh and the fact that you can create a unique clustered index on a NULLable column, but you can't create a PRIMARY KEY on that column).

In fact there are many cases where you want to completely separate the clustered index from the PRIMARY KEY. If you have a table where the PK is a GUID, for example, and you are typically running date range queries against the table, you are probably better off having the PK be non-clustered and have a clustered index on a naturally increasing column (the datetime column) - both to minimize page splits on heavy insert activity and also to best assist date range queries. The non-clustered index will be perfectly fine for looking up individual GUIDs. (I wanted to mention that because a lot of people think the primary key has to be clustered. Not true.)

Also interesting to note that if you create a PRIMARY KEY constraint, then create a unique clustered index with the same name using DROP_EXISTING, the is_primary_key column will still be 1 and Object Explorer will still show the index name under Keys.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
1

Here is one scenario - a lot of code to data mapping frameworks look at the database metadata (what are the primary keys, foreign keys, etc) to determine how code is executed. For example Hibernate requires a primary key.

A typical scenario might be generating a where clause for an update.

Community
  • 1
  • 1
chue x
  • 18,573
  • 7
  • 56
  • 70
  • So Hibernate can't even pick a unique index if it's the only unique index on the table? I could understand it getting confused if there are multiple, but it seems like a temporary oversight to require an explicit primary key. Do you know if this is a permanent limitation, or just another one of these silly things they'll eventually fix? – Aaron Bertrand Mar 31 '13 at 16:14
  • @AaronBertrand - Generally the approach for frameworks is to look for the primary key, as this is what the normal case is when creating a table. Some frameworks do look at unique indexes. However, I can't speak for that particular project (Hibernate) as to what they are planning. What you mention can certainly be done. It is just a matter of how far down the priority list it is. – chue x Mar 31 '13 at 16:26