Reset all IDs in a Large Table

Question

Consider a table that contains around 9,000,000 rows. Each row has a int-based "ID" IDENT column whose value automatically increments on each insert. The table undergoes a lot of "churn," that is to say, a good number of the rows are deleted and added to this table in the course of a day. Although the total number of rows isn't growing at a particularly high rate, the value of the numeric ID assigned to individual rows could conceivably hit the maximum ID value (2^32 ?) at some point over the next couple of years.

Is there some SQL code that can be run to reset all IDs in all rows of the table ? Ideally, if the table contains 'x' number of rows, IDs between 1 and 'x' would be assigned. I'd then re-seed the identity generator to 'x+1'.

The ID column is not a foreign key for any other table.

The process would only need to be performed once every few years so I wouldn't mind running the query in a single-user environment if necessary.

I can't change the type of the ID column to long or varchar to solve the problem. Apparently there is legacy code that depends on the value being a 32bit integer.

I am using SQL Server 2008.

Alright, then.

Answer 1

One option is to create a staging table with a new ID as Row_number(). Consider the below sql:

begin transaction createstaging

select row_number() over (order by existingID) newIDValue, DataColA, DataColB, ...
into staging_table
from source_table 

truncate table source_table

commit transaction createstaging
GO

set identity_insert source_table on 
GO

insert source_table
(existingID, DataColA, DataColB, ...)
select 
newIDValue, DataColA, DataColB, ...
from staging_table 
order by newIDValue

GO

set identity_insert source_table off
GO

drop table staging_Table
GO

The truncate table in combination with the identity_insert should mean that you do not have to manually reset the identity seed on the source table.