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.

  • 2
    So you're going to start over at 1 and then you'll have to repeat this process in a short matter of time. Why not stop using the surrogate ID as an actual identifier for these users? They shouldn't care if the row is identified by 42 or 42,000,564. Nov 13 '12 at 18:21
  • Agree with @AaronBertrand - If you are OK with resetting at an arbitrary time then uniqueness doesn't actually matter in your process.
    – JNK
    Nov 13 '12 at 18:21
  • Uniqueness still matters, it is just the way that the data migrates through time. The software still uses those Id's, hence uniqueness, but the user wants those id's to reset so that they are back in the range of being easy to say. It is far too late in the lifetime of this application to change code, so it is what it is.
    – Mranz
    Nov 13 '12 at 18:33

The easiest way would be to start a new table. Inject the 30,000 rows you want to keep, then drop the old table and rename the new table.


CREATE TABLE dbo.copy_of_foo
  col2 ..., ...

INSERT dbo.copy_of_foo(col2, ...) 
  SELECT TOP 30000 col2, ...
  FROM dbo.foo
  ORDER BY ...;

DROP TABLE dbo.foo;

EXEC sp_rename N'dbo.copy_of_foo', N'foo', N'OBJECT';


You may need to nest the order by and add OPTION (MAXDOP 1) if you want to ensure that you get the 30K most recent rows and the IDENTITY values are assigned in order from oldest to newest, e.g. hopefully you have some other column, such as a date time column, that can help identify the 30000 rows you want to keep:

INSERT dbo.copy_of_foo(col2, ...)
  SELECT col2, ...
    SELECT TOP 30000 col2, ...
      FROM dbo.foo 
      ORDER BY date_time_column DESC
  ) AS x
  ORDER BY date_time_column

Keep in mind though that if someone reported an exception yesterday with row #42,000,564, good luck finding it today. As I suggested in the comment, maybe you shouldn't be exposing these obviously-otherwise-absolutely-meaningless surrogate identifiers to users.

  • As stated in my comment, the id's aren't meaningless, they just happen to be dual purposed, and their usage wasn't a problem until the new requirement came about a couple years later. The customer is fine with said id's resetting, because they are asking for it. I am assuming that they have already accounted for the change in their QA process.
    – Mranz
    Nov 13 '12 at 18:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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