I have a customer that has a table in the database with an autonumber id field. The customer uses that id as a user-friendly identifier to a unique record stored in the database. After some time, my application will remove old records in the database, such that there is usually a sliding window of about 30k records at any given time.
This functionality is fine, and has been working for a few years, but now the id's in the database are up in the hundred millions, and it is harder for the customer to now communicate failure id's to the outside world.
What they want to do, and what I am hoping to find out how to do, is to take all of the records in the current table, and reduce their id to start at 1, and then reseed the identity to start counting where the new id would be.
In math: new id = current id - min(ID)
And then finally: new seed = max(id)
To just help clarify one thing, it's that records are getting regularly deleted from the database so the start:
1-30k Valid Records
After 5 months:
1-2mil - no records 2mil - 2mil + 30k - Valid records
What the user essentially wants to do is to subtract 2mil from all of the id's (still unique), and pretend that the previous records never existed at all.