SQL Identity Column out of step

Question

We have a set of databases that have a table defined with an Identity column as the primary key. As a sub-set of these are replicated to other servers, a seed system was created so that they could never clash. That system was by using a starting seed with an increment of 50.

In this way the table on DB1 would generate 30001, 30051 etc, where Database2 would generate 30002, 30052 and so on.

I am looking at adding another database into this system (it is split for scaling/loading purposes) and have discovered that the identites have got out of sync on one or two of the databases - i.e. database 3 that should have numbers ending in 3, doesn't anymore. The seeding and increments is still correct according to the table design.

I am obviously going to have to work around this problem somehow (probably by setting a high initial value), but can anyone tell me what would cause them to get out of sync like this? From a query on the DB I can see the sequence went as follows: 32403,32453, 32456, 32474, 32524, 32574 and has continued in increments of 50 ever since it went wrong.

As far as I am aware no bulk-inserts or DTS or anything like that has put new data into these tables.

Second (bonus) question - how to reset the identity so that it goes back to what I want it to actually be!

EDIT:

I know the design is in principle a bit ropey - I didn't ask for criticism of it, I just wondered how it could have got out of sync. I inherited this system and changing the column to a GUID - whilst undoubtedly the best theoretical solution - is probably not going to happen. The system evolved from a single DB to multiple DBs when the load got too large (a few hundred GBs currently). Each ID in this table will be referenced in many other places - sometimes a few hundred thousand times each (multiplied by about 40,000 for each item). Updating all those will not be happening ;-)

Answer 1

Replication = GUID column.

To set the value of the next ID to be 1000:

DBCC CHECKIDENT (orders, RESEED, 999)

Answer 2

Perhaps somebody used:

SET IDENTITY INSERT {tablename} ON

INSERT INTO {tablename} (ID, ...)
VALUES(32456, ....)

SET IDENTITY INSERT {tablename} OFF

Or perhaps they used DBCC CHECKIDENT to change the identity. In any case, you can use the same to set it back.

Answer 3

If you want to actually use Primary Keys for some meaningful purpose other than uniquely identify a row in a table, then it's not an Identity Column, and you need to assign them some other explicit way.

If you want to merge rows from multiple tables, then you are violating the intent of Identity, which is for one table. (A GUID column will use values that are unique enough to solve this problem. But you still can't impute a meaningful purpose to them.)

Answer 4

It's too risky to rely on this kind of identity strategy, since it's (obviously) possible that it will get out of synch and wreck everything.

With replication, you really need to identify your data with GUIDs. It will probably be easier for you to migrate your data to a schema that uses GUIDs for PKs than to try and hack your way around IDENTITY issues.

Answer 5

To address your question directly,

1. Why did it get out of sync may be interesting to discuss, but the only result you could draw from the answer would be to prevent it in the future; which is a bad course of action. You will continue to have these and bigger problems unless you deal with the design which has a fatal flaw.

2. How to set the existing values right is also (IMHO) an invalid question, because you need to do something other than set the values right - it won't solve your problem.

This isn't to disparage you, it's to help you the best way I can think of. Changing the design is less work both short term and long term. Not to change the design is the pathway to FAIL.

Answer 6

This doesn't really answer your core question, but one possibility to address the design would be to switch to a hi_lo algorithm. it wouldn't require changing the column away from an int. so it shouldn't be nearly as much work as changing to a guid.

Hi_lo is used by the nhibernate ORM, but I couldn't find much documentation on it.

Basically the way a Hi_lo works is you have 1 central place where you keep track of your hi value. 1 table in 1 of the databases that every instance of your insert application can see. then you need to have some kind of a service (object, web service, whatever) that has a life somewhat longer than a single entity insert. this service when it starts up will go to the hi table, grab the current value, then increment the value in that table. Use a read committed lock to do this so that you won't get any concurrency issues with other instances of the service. Now you would use the new service to get your next id value. It internally starts at the number it got from the db, and when it passes that value out, increments by 1. keeping track of this current value and the "range" it's allowed to pass out. A simplistic example would be this.

1. service 1 gets 100 from "hi_value" table in db. increments db value 200.
2. service 1 gets request for a new ID. passes out 100.
3. another instance of the service, service 2 (either another thread, another middle tier worker machine, etc) spins up, gets 200 from the db, increments db to 300.
4. service 2 gets a request for a new id. passes out 200.
5. service 1 gets a request for a new id. passes out 101. if any of these ever gets to passing out more than 100 before dying, then they will go back to the db, and get the current value and increment it and start over. Obviously there's some art to this. How big should your range be, etc.

A very simple variation on this is a single table in one of your db's that just contains the "nextId" value. basically manually reproducing oracle's sequence concept.