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?
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:
- Lock the table;
- Change the type so it's no longer auto increment;
- Create a new column. You're best off making it have no indexes for now as updating the index will slow does the inserts;
- 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;
- Replace the old column with the new one;
- Reset auto-increment and primary key status.
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.
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.