Gopal Rathore (6/25/2010)
Which is better to have either Numeric Candidate Key or Composite Key in the dependent table to make joins faster to execute say for example I have FormulaId field as primaryKey in Master table and FormulaId and RowId as Composite Key in Dependent table so this will be a better solution or I can put a Numeric Candidatekey in Primary and Dependent tables to allow joins faster?
If your only consideration is performance of joins, then most of the time 4 byte's INT or 8 byte's BIGINT key will perform better than a key of a large string (or other) datatype.
There are two main theories exist around what your primary key should be. Many people (myself as well) prefer to have surragate/artificial key as PK (Identity column is fine for this). However, there are many others (Joe Celko's followers) who would tell you to mainly use Natural Keys (and never use Identity and Bit datatypes as they are of too old age :-D). But, as well stated by Joe Celco himself "There is no such thing as a "universal, one-size-fits-all" key.". Here the link to his article about all sorts of keys 😀 http://intelligent-enterprise.informationweek.com/showArticle.jhtml;jsessionid=THPULYB0UUJP5QE1GHRSKH4ATMY32JVN?articleID=201806814
Please Note: I am strongly dissagree with his stance on use of Identity columns and his defenition of lazy, non-RDBMS programmer (and many other of his propoganda) :-D. Saying that this article is worth reading anyway.