Take the 2-minute tour ×
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. It's 100% free, no registration required.

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.

share|improve this question
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. –  Aaron Bertrand 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
    
@Mranz You could define a unique key on any suitable field. And I'm pretty sure that there is no software product in the world which could not be improved. –  dezso Nov 13 '12 at 19:30
    
@dezso I am sure any software product could be improved. But I could point the to vast quantity of legacy software in the world, and say that there is no business case for improving it. Since you are unaware of the business case for my app, I am not sure why you are trying to spend time lecturing me on improving the app. –  Mranz Nov 13 '12 at 20:23

1 Answer 1

up vote 5 down vote accepted

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.

share|improve this answer
    
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

 
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.