Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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 Nov 9 '12 at 7:25
add comment

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
Excellent idea. I was hoping for a built-in "resetAllOfMyIDs()" function or something but this'll work. Thanks again. –  Chad Decker Nov 8 '12 at 14:21
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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