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.

  • With type INT, starting at 1, you get over 2 billion possible rows - that should be more than sufficient for the vast majority of cases. If you use an INT IDENTITY starting at 1, and you insert one row every second (all day long, every day of the year), you need 66.5 years before you hit the 2 billion limit ....
    – marc_s
    Commented Nov 9, 2012 at 7:25

1 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

set identity_insert source_table on 

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


set identity_insert source_table off

drop table staging_Table

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.

  • Excellent idea. I was hoping for a built-in "resetAllOfMyIDs()" function or something but this'll work. Thanks again. Commented Nov 8, 2012 at 14:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.