Identity Columns or UDF that explicitly generates a unique id? I am in middle of a debate about whether it is better to make a PRIMARY KEY out of an Identity Columns, our out of a UDF that explicitly generates a unique id.


I am arguing for the Identity Column.
My partner is arguing for generating the values manually, he claims


by putting the UDF on another table where we can have a UDF


lock the resource
increment an ID table with one field called ID_Value by 1
use this as a global unique identifier

Or have the table do an id+1 when inserting
That it's simpler to move data between servers and/or environments not having the identify constraint; 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. 



Which implementation makes more sense?
Your colleague is an idiot.

The solution won't be scalable, the UDF isn't concurrent (same reason as this). And how do you deal with multi-row inserts: this would require a UDF call per row

And migrating to other RDBMS doesn't happen often in real life... you may as well not use SQL Server now and use sequences on Oracle and hope you don't migrate away.

Edit:

Your update states that moving data is for refreshing non-production databases.

In that case, you ignore the identity columns when refreshing. You don't compromise your implementation to make non-prod loading easier. Or use temp tables to track the identity value changes.

Or use processes: we refresh our test system every night from production which avoids the issue entirely. (And ensures our prod backup can be restored too)