Difference between primary key (cluster) and clustered unique index in SQL Server

Question

I understand what is primary key and unique clustered index my question is why primary key is required when we define unique clustered index. Just considering performance good database design.

As per my understanding when we define a clustered unique index it sorts the data physically and which is required for table performance and it is immaterial whether we define primary key or not

Scarberry

Differences: Well, in a nutshell, the one is a primary key, while the other is not. – Mouser

The primary key is a logical construct - the key used to uniquely and reliably identify each and every single column in a table. The clustered index is a physical implementation detail of how SQL Server handles its storage. – Orose

Answer 1

There is no practical difference between a unique index on non nullable columns and a PK as far as SQL Server is concerned.

Both enforce uniqueness, can be clustered or non clustered, and can be referenced by a foreign key constraint.

Some tools may expect there to be a primary key defined though.

Regarding your point that a clustered index "sorts the data physically" this is a bit of a misconception.

This is only true if the CI has zero fragmentation. For example after page splits it is perfectly possible for the clustered index pages to be out of order in the file.

Canzone

is that what the poster was asking? – Serles

@MitchWheat - The title seems to indicate so, – Canzone

Answer 2

"why primary key is required when we define unique clustered index "

It is not.

By default, when you declare a table with a primary key in SQL Server, it adds a clustered index on the primary key (if you do not specify a separate clustered index).

But the clustered index does not have to be on the columns that comprise the table's primary key.

You can create a table where the primary key has a unique non-clustered index, and the clustered index is elsewhere.

Serles

... and you can have a table without any clustered index. – Burgoo

indeed - called a heap. – Serles