SQL Server Reseeding Identity Values

Maybe it just me, but when working in ETL processes I find it necessary to reseed the identity seed value. I find gaps appearing Most of the time this is caused by rolling back data that came in bad. Before I import any data I run the script below to perform a reseed. It’s fairly simple but saves head-aches, particularly on large DB’s, where the threat of hitting 2 billion or the max on a int is possible. Here the PKInt represents the identity column I’m reseeding.




Declare @MaxSeed int
Select @MaxSeed = max(Pkint) from ImportTable
DBCC CHECKIDENT ('ImportTable', RESEED, @MaxSeed )