Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am in middle of a debate about whether it’s better to use identity columns as a primary key on a DB table or have a generated value manually created.

I am arguing for the sql managed identity column because there will be no contention created and keep overhead down.

My partner is arguing for generating the values manually via another table where we can have a UDF lock the resource increment an ID table with one field called ID_Value by +1 and use this as a global unique identifier. Or have the table do an id+1 when inserting. The logic here is that it would be simple to move data between servers and/or environments not having the identify constraint.

Without running tests on his solution I doubt it is at all scalable because all inserts are not bottle necked via this id generation, or each table id generation.

Also, I don’t believe moving chunks of records is all that difficult is it, even with the identity constraint?

Who is right and please detail the exact reasoning.

EDIT: when I say moving databases or servers or environments i mean moving from one DB where there is data to another similar DB with lets say staging or dummy data. For testing in non prod we may want to pull all records from yesterday down to staging for testing. this is what i meant with moving data.

EDIT ~ drachenstern For those following along, this question was dual-posted, migrated from SO, and merged once it got to this site. Just trying to keep valid conversations on one place.


merged by jcolebrand Mar 10 '11 at 18:59

This question was merged with Which is better: identity columns or generated unique id values? because it is an exact duplicate of that question.

migrated from Mar 10 '11 at 17:34

This question came from our site for professional and enthusiast programmers.