CODEWITHSUNDEEP
sql-serverdatabase-indexes
DRastislav
DRastislav

Reputation: 1882

Possible performance change if column NOT NULL UNIQUE CLUSTERED INDEX will be Primary Key

If column is declared as NOT NULL UNIQUE CLUSTERED INDEX, will making it PRIMARY KEY do any actual change on performance or anything?

Upvotes: 0

Views: 66

Answers (2)

Alfin E. R.
Alfin E. R.

Reputation: 928

PRIMARY KEY does not allow null and has to be unique, constraints NOT NULL UNIQUE also may apply the similar rule into the column as PRIMARY KEY does, however, your table can only have one primary key, but could have multiple NOT NULL UNIQUE Columns.

and by NOT NULL UNIQUE CLUSTERED INDEX and PRIMARY KEY, both will be stored, ordered, and structured as in the CLUSTERED INDEX, so in operational performance basis for select, insert, update, or delete, there should be no different

the only difference might be on the metadata itself.

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239754

It should not have any effect. To within a few handwaves1, a PRIMARY KEY is the same as a UNIQUE KEY, with the additional proviso that all columns are forced to be non-NULL. As such, decorating this column as the PRIMARY KEY should not result in any change in the physical data structures2 that implement the table.

CLUSTERED is a completely separate concept - sometimes conflated with PRIMARY KEY. If a table has no CLUSTERED INDEX and a PRIMARY KEY is added, then by default, that will also then be defined as a CLUSTERED INDEX. But this behaviour can be overridden if required, and the two really aren't tied together in practice.

1I actually can't think of any relevant differences at this point but this is just to avoid nitpicking, hopefully.

2The clustered index and any non-clustered indexes and statistics.

Upvotes: 2

Related Questions