SQL Server Performance

Primary Key vs Unique Clustered Index

Discussion in 'SQL Server 2005 General Developer Questions' started by rogilvie, Nov 19, 2007.

  1. rogilvie New Member

    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
  2. martins New Member

    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).
  3. satya Moderator

    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
  4. Adriaan New Member

    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.
  5. rogilvie New Member

    Thanks for clearing that up
    R
  6. ndinakar Member

    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.
  7. DilliGrg Member

    Also, don't forget about the FOREIGN KEY constraint by having PK in one table.
  8. Adriaan New Member

    [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.
  9. DilliGrg Member

    [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.
  10. Adriaan New Member

    If you have both a natural key and an identity column, the identity column can be the clustered index, the natural key can be the PK, and FKs can refer to the identity column.

Share This Page