Sql-server – How to reset all of the identity values for a table, and reseed the auto-number in Sql Server 2008

ddlidentitysql serversql-server-2008

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)

Thanks

edit

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.

Best Answer

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.

BEGIN TRANSACTION;

CREATE TABLE dbo.copy_of_foo
(
  ID INT IDENTITY(1,1) PRIMARY KEY,
  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';

COMMIT TRANSACTION;

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, ...
  FROM 
  (
    SELECT TOP 30000 col2, ...
      FROM dbo.foo 
      ORDER BY date_time_column DESC
  ) AS x
  ORDER BY date_time_column
  OPTION (MAXDOP 1);

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.

Related Question