CODEWITHSUNDEEP
sql-serversql-server-2008auto-increment
Jordan Reiter
Jordan Reiter

Reputation: 21022

DBCC CHECKIDENT RESEED -- is new value required?

All the documentation I read about reseeding suggests something along the lines of:

  1. SET @maxIdentityValue = (SELECT MAX(id) FROM tablename)
  2. run DBCC CHECKIDENT('tablename', RESEED, @maxIdentityValue)

And yet it appears to me that a simple DBCC CHECKIDENT('tablename', RESEED) is all that's needed, and it will automatically determine the correct identity value from the table without supplying a max value.

Is there a reason (performance or otherwise) that extracting the value using MAX first is preferred?

Piggyback question: the reason I need to reseed is because I'm using replication and identities keep getting set to Null each time the database replication runs. What am I doing wrong? How can I maintain the correct identity seed for each table?

Update (Current solution)

For now I'm not using the max value. This is the stored procedure I'm using (I generate it using a query on sys.columns and then just cutting and pasting each into a new query window. Messier, slower, less elegant, but I'm not very familiar with stored procedures and don't want to use dynamic SQL queries):

declare @seedval integer
declare @maxval integer
declare @newval integer
set @seedval = (select ident_current('mytable'));
set @maxval = (select MAX(id) from mytable);
if @maxval > @seedval or @seedval is NULL
BEGIN
    print 'Need to reseed: max is '  + cast(@maxval as varchar) + ' and seed is ' + cast(@seedval as varchar) 
    dbcc checkident('mytable', RESEED);
    set @newval = (select ident_current('mytable'));
    print 'Max is ' + cast(@maxval as varchar) + ' and seed is ' + cast(@newval as varchar) 
END 
ELSE
    print 'No need to reseed';

Upvotes: 7

Views: 38214

Answers (3)

101010
101010

Reputation: 15736

(I'm reposting my answer from this other SO page)

Perhaps the easiest way (as crazy as this sounds and as code-smelly as it looks) is to just run DBCC CHECKIDENT twice like this:

-- sets all the seeds to 1
exec sp_MSforeachtable @command1 = 'DBCC CHECKIDENT (''?'', RESEED, 1)'

-- run it again to get MSSQL to figure out the MAX/NEXT seed automatically
exec sp_MSforeachtable @command1 = 'DBCC CHECKIDENT (''?'')'

Done.

If you want, you can run it once more to see what all the seeds were set to:

-- run it again to display what the seeds are now set to
exec sp_MSforeachtable @command1 = 'DBCC CHECKIDENT (''?'')'

This is just a creative way to take advantage of the comment from the documentation:

If the current identity value for a table is less than the maximum identity value stored in the identity column, it is reset using the maximum value in the identity column.

Upvotes: 4

mohas
mohas

Reputation: 1931

As it is stated in MSDN, it is fairly enough to use just:

 DBCC CHECKIDENT('tablename', RESEED)

most of the time, however there are these two conditions where it will not work:

  • The current identity value is larger than the maximum value in the table.
  • All rows are deleted from the table.

in which you have to go with they way that you mentioned (select max(id) and the rest), so why bother in the first place? :)

Upvotes: 9

Aaron Bertrand
Aaron Bertrand

Reputation: 280381

There are cases where you might want to determine the max so that you can reseed and leave a gap (e.g. max + 100). One case might be when you have multiple copies of a table and you are going to distribute independent but mutually exclusive identity ranges from them.

But still, I'm not confident that the RESEED without a parameter will work correctly in all scenarios.

Is it a common occurrence that you're reseeding tables back to the max? Why? Poorly coded application that generates a bunch of rows in a loop that you end up rolling back?

In any case, you'll want to wrap the MAX and RESEED in a transaction to prevent the chance that a user will insert a new row after you've taken the max but before you've issued the reseed.

Upvotes: 2

Related Questions