July 25, 2014 at 12:15 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing post 16 (of 15 total)
You must be logged in to reply to this topic. Login to reply