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. It's 100% free, no registration required.

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 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.

share|improve this question
I'm not sure your partner uses the term global unique identifier in the usual way. I miss either the function newid() or newsequentialid() mentioned, which are usually used to generate global unique identifiers. Please give more details about his proposal. – bernd_k Mar 8 '11 at 20:34
@bernd_k: see my edit – kacalapy Mar 8 '11 at 20:42
So using his way, on dev I am at record 1000 and I add a test record and get 1001. I then want teh prod data which is at 2000. Seemes like I still havea conflict for record 1001. There is NO ADVANTAGE to creating your own numeric values except not having gaps which ioccasionally needed for regulatory or legal reasons. It is also really easy to get it wrong and have run conditons that cause you to lose data integrity when you roll your own. – HLGEM Feb 18 '13 at 21:11
up vote 17 down vote accepted

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.


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)

share|improve this answer
+1 for giving me a chuckle with your frank analysis. :-) – Joe Stefanelli Mar 8 '11 at 20:22
@Joe Phillips: why not change -1 to 0 then? It is idiocy as my UDF concurrency link points out, which meets the OP's criteria for "exact reasoning" against a non-identity solution – gbn Mar 8 '11 at 20:25
@gbn: see my edit... the bonus to his idea is no hassle moving records of a day, or week, from one DB for a application to the same db with different data on another server – kacalapy Mar 8 '11 at 20:36
@kacalapy: see my update too please. This is arguably a worse reason! – gbn Mar 8 '11 at 20:38

Use an identity value. Generating your own sequence table and sequence values will take a lot of overhead and cause a lot of locking and blocking while trying to generate numbers.

Identity exists for a reason, use it.

When SQL Denali comes out it will support sequences which will be more efficient than identity, but you can't create something more efficient yourself.

As for moving records from one environment to another either turn IDENTITY_INSERT ON when doing the insert, or check the box in SSIS.

share|improve this answer
+1 Good answer and the inclusion of IDENTITY_INSERT_ON will be helpful in this case. – Matt M Mar 9 '11 at 13:41
I disagree. If they just use INT IDENTITY then they're going to have collisions when they go to move from one database to another one. – jcolebrand Mar 10 '11 at 4:03
Very few database applications can be easily moved from one database platform to another. If you are trying to ensure portability from one database to another you are loosing so many of the possible features of the database engine, that this is the least of your problems. There will be so many other changes, and changing from an identity column to a proper sequence (like SQL "Denali" will support) is a fairly small change. Doing it yourself will be very heavy on the IO for a heavily loaded table, not to mention you can't insert multiple rows at once. – mrdenny Mar 10 '11 at 6:04
that wasn't my point. If you move from "production" to "test" and have an identity field, you run the risk of overwriting or colliding on data. That's all I'm saying. Yeah, it shouldn't be an issue in that direction, but I'm just saying that it could happen. – jcolebrand Mar 10 '11 at 16:47
True, you'll have the same number for different row values in dev, test, qa, uat and production. So what? If those values are important (like for a lookup table) then hard code them manually, which shouldn't be a problem as you shouldn't be putting values in those tables very often. If you need to control the identity values between the environments to avoid collisions then reset the identity values between the environments when you restore from production. – mrdenny Mar 11 '11 at 6:49

The identity column sounds fine to me. I'm not sure I follow the logic about why it's difficult to move data between servers.

If you do want each row to have a globally unique identity you can use a UUID but I wouldn't do it unless you are sure that the global uniqueness is necessary - usually it's not. Using UUIDs as ids will decrease performance, increase disk space requirements and make debugging harder - because of the length it is difficult to remember a UUID, tell it to someone over the phone, or write it down on paper without error.

share|improve this answer

for simple numeric IDs, just go with identity and forget all the problems of manually generating them.

you can always create a "super table" that uses an idetity as the PK and have a type column, and any other info. When you need a new ID (assuming you mean unique IDS across different tables) just insert into this table and grab the SCOPE_IDENTITY() and then insert into the actual table you need.

share|improve this answer
can you explain your SCOPE_IDENTITY() solution in more detail please. – kacalapy Mar 8 '11 at 20:34
basically you create a table: MasterIDs with an identity PK, when you need to insert a row into your Table1, INSERT INTO MasterIDs and get the identity generated by that row using SCOPE_IDENTITY() and then insert into Table1 using that value as the PK. Table1 will have a non-itentity int PK. You would do the same process to insert into Table2, etc. Let SQL Server manage the identity values in the MasterIDs table, which you can then use in your other tables. MasterIDs could containother tables, like type (so you could know what table, Table1 or Table2, etc, uses that identity value. – RacerX Mar 8 '11 at 20:42

As long asyou are using foreign key constraints properly (cascading, updating, etc) then you will be fine with using an identity field. I really don't see an advantage to the other solution in this case.

share|improve this answer

Identity was made to fit your scenario. You have tools like replication for server/environments data exchange that keep it all together.

share|improve this answer

Sorry for digging up a 4 year old thread but I had to put my two-penneth in.

I have just finished a piece of work where I have replaced a SQL Server identity column with a normal int field and controlled Id allocation myself. I have seen quite impressive performance gains. Unlike the op I haven't got a UDF to generate the id. But the principle is pretty much the same. There is part of the software which maintains a pool of id's. When they run out it gets another batch by querying the database for the next Low value and increments this to the next High.

This allows us to generate ids and relate all entities outside of a transaction in our ORM before we submit the batches to the database and also submit larger batches without extra roundtrips to get the identity just inserted (required by identity columns).

share|improve this answer
Do you realize you can use a simple SEQUENCE object for that purpose, assuming you are using SQL Server 2012+? – Max Vernon Sep 30 '15 at 1:25
Yes I am. However, in the id table we have there is more than one row. Allowing us to use specific ranges if we wish. i.e. for reusing deleted blocks and negative id's – Mark Sep 30 '15 at 9:00

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.