how to keep away from identity as primary key

Question

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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

George M Parker SSCertifiable Points: 5412 More actions · Answer 1

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: 1003943 More actions · Answer 2

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)