CODEWITHSUNDEEP
sql-serversql-server-2008indexingunique-constraintclustered-index
Yaron Levi
Yaron Levi

Reputation: 13087

Non nullable column with clustered unique index. Why need Primary Key?

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

Upvotes: 1

Views: 877

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280625

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.

Upvotes: 5

chue x
chue x

Reputation: 18823

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.

Upvotes: 1

Related Questions