1

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?

Maksym Gontar
  • 22,765
  • 10
  • 78
  • 114
David.Chu.ca
  • 37,408
  • 63
  • 148
  • 190

3 Answers3

8

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 1011 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.
cletus
  • 616,129
  • 168
  • 910
  • 942
2

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.

Al W
  • 7,539
  • 1
  • 19
  • 40
0

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.

Tom H
  • 46,766
  • 14
  • 87
  • 128