Surrogate key or multi-column natural key - performance question ...

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Joergen Bech (jbech_at_post1.tele.dk)
Date: 12/07/04 

Date: Tue, 07 Dec 2004 10:44:19 +0100

Don't want to open the big can of worms called
"surrogate vs. natural keys". Please, no flames :)

Question is: Suppose I have master/detail tables
with the master tables having 3-to-5 column natural
primary keys. Suppose those columns were fairly short, i.e.
10-20 bytes total. Would it make sense to add surrogate
keys (uniqueidentifier - not 4-byte identifier ones) and
use those as primary keys instead? For detail lookups,
this, I think, would simplify coding a great deal, but what
about performance? Is it just as fast using multi-column
natural keys than uniqueidentifier surrogate keys, provided
the total length is less than (or at least not much greater than)
that of a uniqueidentifier column? Or is there a significant
cost associated with using multiple columns?

Anyone who have done some real-life tests on this subject?

TIA,

JB

Relevant Pages

  • Re: Whats the best practice for primary keys?
    ... The idea behind surrogates is introduced not by Codd, but by Hall, Owlett & ... the surrogate values) and an e-relation (the corresponding relation with the ... The need for logical surrogates in relational databases is genuine; ... compound keys have missing values in part of the referencing columns. ...
    (microsoft.public.sqlserver.programming)
  • Re: Set Based Row Duplication
    ... Surrogate Keys are keys in the usual Relational sense but have the following specific properties: ... Their values serve solely as surrogate for the entities they stand for ...... ... When a new entity is inserted into the database, it is given a surrogate key value that has never been used before and will never be used again, even if the entity in question is subsequently deleted. ... A quote from Dr. Codd: "..Database users may cause the system to ...
    (microsoft.public.sqlserver.programming)
  • Re: Set Based Row Duplication
    ... WHAT IS A SURROGATE KEY? ... A quote from Dr. Codd: "..Database users may cause the system to ... "There are three difficulties in employing user-controlled keys as ...
    (microsoft.public.sqlserver.programming)
  • Re: Access Many to Many relationship
    ... Base the main form on TblProject and ... base the subform on TblProjectEmployee. ... key autonumber for join tables along with composite keys. ... In our shop we use surrogate Autonumber/Identity keys almost ...
    (microsoft.public.access.tablesdbdesign)
  • Re: A real world example
    ... Design criteria for choice of candidate keys include: familiarity, irreducibility, simplicity and stability. ... I disagree that the concept of surrogate vs. natural is useful. ... Twenty years ago there was some discussion of surrogate keys vs. natural keys, but further reflection reveals that natural keys are nothing more or less than familiar surrogates. ... There really is little more to discuss about natural keys vs. surrogates except that self-aggrandizing ignorants periodically appropriate the terms in the nonsense they spout. ...
    (comp.databases.theory)