Post reply

Non-Clustered, Composite Primary Key

  • July 25, 2014 at 12:15 pm

    #1732488

    TomThomson (7/25/2014)

    Brandie Tarvin (7/23/2014)

    Not to provoke debate (but I'm gonna anyway @=), I generally use surrogate Identity columns as singular, non-composite PKs because if I used natural composite keys, I'd be including half the columns in most of our tables.

    So do I, if I'm starting from scratch. But I will also have a uniqueness constraint for the natural key and non-null constraints for each of its columns, simply because I want to be in a decent normal form (actually to be 1NF according to the original definition of a primary key).

    If however I'm starting from an existing database that doesn't use a surrogate key and has problems because there is nothing suitable for a clustering key I will introduce a unique identity as a clustering column, to avoid either using a bad clustering key or living with a heap. That doesn't change the primary key.

    If there are size problems caused by using the natural key in forign key constraints and in join conditions, that is a separate problem, which needs the same unique identity and also some code and constraint changes. But it mayalso need to avoid clustering on the identity instead of on the natural key if the workload consists mainly of queries which operate on contiguous ranges of the natural key. UNfortunately I can't always win both on the swings and on the roundabouts.

    Quite right. What is critical is the best clustering index, not which column(s) you designate as the formal PK.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Viewing post 16 (of 15 total)

You must be logged in to reply to this topic. Login to reply