how to keep away from identity as primary key

• Sarsoura

  Ten Centuries

  Points: 1220

  More actions

  November 8, 2012 at 10:44 am

  #265185

  Hello,

  I know that if my primary key is identity it slows down the system

  I am designing a new database.

  two of the tables are question (questionid int (PK identity), questionnarrative, etc)

  Quiz (quizid int(PK,identity), quizname, etc

  I will have a huge huge number of questions and quizzes

  Is there a way to keep away from identity and it is better to specify the column as largeint instead of int

  Thanks

• George M Parker

  SSCertifiable

  Points: 5412

  More actions

  November 8, 2012 at 10:53 am

  #1557292

  I know that if my primary key is identity it slows down the system

  Where did you hear that from? First, are you concerned about an identity column being used as a primary key, a clustering key or both? They don't necessarily have to be the same.

  As a general rule, I recommend the Kimberly Tripp recommended best practice of making the clustered index an identity column.

  You can read her blog here: http://www.sqlskills.com/blogs/kimberly/post/The-Clustered-Index-Debate-again!.aspx.

• Jeff Moden

  SSC Guru

  Points: 1004318

  More actions

  November 8, 2012 at 11:04 am

  #1557297

  Sarsoura (11/8/2012)

  ---

  I know that if my primary key is identity it slows down the system

  Not sure where you heard that from but that's the diametric opposite of what usually happens.

  See the link that George provided above for a primer on the subject.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)