Reset primary key (int as identity)

Question

I have a table with a primary key as bigint (identity property is Yes and staring from 1 by 1). This table is in production and has been updated on daily bases: lots of deleting and inserting.

The problem is that this key is growing too big right now with 8 digits. I worry about overflow one day eventually.

Fortunately, this key is not used as foreign keys to any other tables. It is just used to identify a row in the table. Therefore I can safely reset the key values starting from 1 again, maybe once a year.

I could create a blank table and copy other field data there, then remove all the rows in the original table, reset the key/table and finally copy data back.

Not sure if there is if there is a build-in sp_xxx available in Microsoft SQL 2005 to do the job: just to reset primary key in sequence starting from 1 without affecting other column data? Or any other simple solution?

Answer 1

The maximum value for a bigint is 9,223,372,036,854,775,807. If you'd gotten to 8 digits in a day you'd still need 10¹¹ days to hit the max. That's like 25 million years.

Assuming you still want to reset the column, the first question I have is: is the ordering of rows important? Meaning do you rely upon the fact that row 1000 comes before 1100 for, say, chronological or otherwise absolute ordering? If not, it's easy: delete the column an add it again. Hey presto, new values.

If you need to maintain the order you'll need to do it a little more carefully:

1. Lock the table;
2. Change the type so it's no longer auto increment;
3. Create a new column. You're best off making it have no indexes for now as updating the index will slow does the inserts;
4. Populate the values in the second with a loop of some kind incrementing a counter (like the SQL Server rownum trick) ordering the inserts to match the original order;
5. Replace the old column with the new one;
6. Reset auto-increment and primary key status.

Answer 2

If you're using a BIGINT, you're not even close to overflowing it. If you're only at 10,000,000 after a year, you could go for a million years and still be fine.

Answer 3

Thanks for your posts!

Answer 4

by the way, how about int?

Answer 5

make a new table with a different name, but exactly the same columns. do a insert into new_table select from old_table. then drop the old table and rename the new table.