Primary Key vs Unique Clustered Index

Morning All,
I was wondering if somebody could explain if there is any significant benefit from defiing a primary key constraint on a table as oppose to a unique clustered index on a non-nullable column. Are the two approaches equivalent? Why would you choose to do one over the other?
Thanks
R

Hi,
Well the behaviour of the unique clustered index would be exactly the same as defining a primary key, so in my opinion it is no different from creating a primary key and there would be no benefit using one above the other.
From a best practise perspective I would rather define it as a primary key, as that is in fact what you are creating (it is just not labelled as that).

When you specify a PRIMARY KEY constraint for a table, the SQL Server 2005 Database Engine enforces data uniqueness by creating a unique index for the primary key columns. If a PRIMARY KEY constraint is defined on more than one column, values may be duplicated within one column, but each combination of values from all the columns in the PRIMARY KEY constraint definition must be unique.
See more on http://msdn2.microsoft.com/en-us/library/ms175132.aspx here

I was expecting MS Access to have a problem with an ODBC-linked table without a PK, but actually it represents the unique constraint as if it was the PK.
If there is no unique constraint, then the Jet engine will not allow inserts, updates or deletes.
But for the sake of clarity I would suggest to always include a PK for permanent tables.

If the columns are non-nullable then PK is same as unique-clustered index. technically, the unique index allows NULLs and PK doesnt. So if you have a unique-clustered index on, lets say, 4 columns.. you can have a different combination of NULLs, but if they were a PK, then SQL Server doesnt allow NULLs.

[quote user="DilliGrg"]Also, don't forget about the FOREIGN KEY constraint by having PK in one table.[/quote]You can have an FK refer to a (set of) column(s) that has a unique constraint, which could be a PK or a unique index - either way.

[quote user="Adriaan"]
[quote user="DilliGrg"]Also, don't forget about the FOREIGN KEY constraint by having PK in one table.[/quote]You can have an FK refer to a (set of) column(s) that has a unique constraint, which could be a PK or a unique index - either way.
[/quote]
Can be done but looks pretty unusual to me.