pseudo primary key vs composite

• Mike Is Here

  Hall of Fame

  Points: 3348

  More actions

  April 20, 2009 at 6:56 am

  #208393

  I am looking for an article or paper for (or against) composite keys or alpha keys vs numeric keys.

  We have an address table that looks like

  WhoId int

  TableName varchar(128)

  AddressType varchar(20)

  {Address Information}

  Then there are several tables that have a simple identity column primary key.

  So that primary key would link to WhoId and Table Name of the table, because right now the identity column value can be duplicated across multiple tables.

  Here are the question of the debate?

  Should there be a composite key on the Address table (WhoId, TableName, AddressType) or a pseudo primary key of identity and make the three fields a unique constraint?

  Should the keys in the other table be as they are numeric or should we work on building a unique key per table like a two character code and a sequential number? People have stated that querying off of alpha is slower but it would eliminate the need for a TableName column?

  Thank you

• Mohit K. Gupta

  SSChampion

  Points: 12130

  More actions

  April 20, 2009 at 1:36 pm

  #981006

  By default primary keys are unique and clustered; if you create it on such wide key it will be very expensive primary key. Unless you create your primary key as non-clustered and another candidate key as clustered I would no recommend it.

  You can read Kimberly's article, here. She gives a bit of overhead associated with clustered primary keys.

  Thanks.

  Mohit.

  [font="Arial"]---

  Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
  Microsoft FTE - SQL Server PFE

  * Some time its the search that counts, not the finding...
  * I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

  How to ask for help .. Read Best Practices here[/url].