I am in middle of a debate about whether it is 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?
Which implementation makes more sense and can you 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 production we may want to pull all records from yesterday down to staging for testing. This is what I mean with moving data.