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 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
up vote 20 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.

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)

share|improve this answer

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

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-identity 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 contain other tables, like type (so you could know what table, Table1 or Table2, etc, uses that identity value.

share|improve this answer

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

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

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

share|improve this answer

Your Answer

 
discard

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.