Clustered constraint v clustered index

I inherited a database where the designer uses unique clustered constraints instead of indexes for primary keys.

Now as far as I can make out, there is no performance issue because it is clustered. My question is, why only create a unique clustered constraint instead of a unique clustered index?

My thinking is that a primary key should be ceated as an index rather than a constraint. Does it matter?

Hi Kenster.

A unique clustered constraint, a unique clustered index, and a primary key are all really different objects/types.

A primary key is a constraint, so when you create a primary key, you'll end up with a primary key constraint to enforce it...a primary key uses a special unique constraint behind the scenes as well.

A unique constraint (whether clustered or not) uses a similar structure as a unique index (again, whether clustered or not) to enforce uniqueness (and to provide seeking when appropriate)...i.e. a b-tree structure...

In reality, the differences between creating a unique index and a unique constraint are pretty minimal (there are a few though...just do a web search for it and you'll find lots of articles discussing it).

HTH,

|||So is one better than the other when creating a primary key?|||

When you create a primary key, you have no option - you get a primary key constraint, period...behind the scenes, you end up with a unique index on the columns that make up the key and absolutely no null values are allowed.

As for a unique constraint vs. unique index, it's not to say that one is any better than the other, they both provide very, very, very similar functionality - If you create a unique constraint, you end up with a unique index behind the scenes.

Differences between constraints vs. indexes:

The columns that make up the unique key can allow nulls but not for more than one complete key.

A unique key can be referenced by a foreign key constraint and a column which has only a unique index cannot be referenced.

Constraints are checked before indexes and this can lead to a large multi-row insert/select or update to fail before modification.

There's probably some more as well...