Identity Columns

Question

Post reply

Identity Columns

r.mitchell

Old Hand

Points: 368
More actions
May 11, 2012 at 10:33 am

#273890

I'm having an issue trying to reseed a identity column. I can only get it to reseed if I specify a value, but I just want it to reseed at the highest value in the column. Which I thought was the default behavior with DBCC CHECKIDENT. Here's a little code that I threw together to show what I am trying to do but with a temp table.
CREATE TABLE #Temp
(
id_Num int identity(1,1),
aLetter char(1)
)
INSERT INTO #Temp (aLetter) VALUES
('a'), ('b'), ('c'), ('d'), ('e'), ('f'), ('g'), ('h')
DELETE FROM #Temp
DBCC CHECKIDENT('#Temp', RESEED)
INSERT INTO #Temp (aLetter) VALUES ('i')
DBCC CHECKIDENT('#Temp', RESEED)
The output shows the following :
Checking identity information: current identity value '8', current column value '8'.
Checking identity information: current identity value '9', current column value '9'.
Maybe I'm not understanding the DBCC CHECKIDENT function correctly, but if anyone can show me how i can get it to reset to the max value after a delete I'd be very grateful. Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Matthew Darwin SSCertifiable Points: 5513 More actions · Answer 1

You need to add the value you wish to reseed it to, so:-

DBCC CHECKIDENT ('#Temp', RESEED, 8)

Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

You'd actually need to use CHECKIDENT twice. The first time resets the next ID to an arbitrarily low value, and the next reseed puts it to the next higher value that isn't in use.

So:

DBCC CHECKIDENT('atable', RESEED,0) --resets the ID to 0

DBCC CHECKIDENT('atable', RESEED) --resets to the next higher value in table

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

r.mitchell Old Hand Points: 368 More actions · Answer 3

Thanks. I was re-reading the msdn site. Guess I didn't pay enough attention that it only resets it to the max column value if the identity is less that the max value, not if it is above it.