Change the seed value of an existing table

Question

Post reply

Change the seed value of an existing table

Sree-258588

Mr or Mrs. 500

Points: 533
More actions
November 19, 2010 at 11:25 pm

#243070

Hi
I've table with THE following structure:
id int identity(1,1)
name varchar(100) not null
id is the primary key
now I want to change the identity (0,1)
set the seed value to 0
I've tried using dbcc checkident (reseed, table1, 0)
but this doesn't change in the table actually.. wehn i see the sp_help table1 is still find it with 1,1.
Please help me out to change the existing identity seed value
Thanks in advance

Viewing 6 posts - 1 through 5 (of 5 total)

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

Just to do a syntax confirmation, it's DBCC CHECKIDENT ( table1, reseed, 0). You have that flipped above.

Is the table truncated, or are there rows in it?

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 2

Craig Farrell (11/19/2010)
Just to do a syntax confirmation, it's DBCC CHECKIDENT ( table1, reseed, 0). You have that flipped above.
Is the table truncated, or are there rows in it?

Edit: A little fun with google, and I find your answer.

http://msdn.microsoft.com/en-us/library/aa258817%28SQL.80%29.aspx

If no rows have been inserted to the table since it was created, the first row inserted after executing DBCC CHECKIDENT will use new_reseed_value as the identity. Otherwise, the next row inserted will use new_reseed_value + 1.

So, unless the table is either drop/created, or truncated, it's behaving as designed.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Perry Whittle SSC Guru Points: 234013 More actions · Answer 3

Sree-258588 (11/19/2010)
Hi
I've table with THE following structure:
id int identity(1,1)
name varchar(100) not null
id is the primary key
now I want to change the identity (0,1)
set the seed value to 0
I've tried using dbcc checkident (reseed, table1, 0)
but this doesn't change in the table actually.. wehn i see the sp_help table1 is still find it with 1,1.
Please help me out to change the existing identity seed value
Thanks in advance

I'm assuming you want to check the current seed value in the table, if so use the following

DBCC CHECKIDENT (table1, NORESEED)

For info, truncate will automatically reset the seed to the table default, in this case (1, 1).

This translates to (next value to use, increment value).

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Sree-258588 Mr or Mrs. 500 Points: 533 More actions · Answer 4

Thanks for your reply.

So is there no way that I can change the identity value after it is created once ?

as after doing the dbcc checkident...

I don't see the changes in the sp_help it still showing IDENTITY - SEED - 1.

Thanks in advance.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 5

Sp_help shows you the default which looks like it's using the default (1, 1). This happens if you do not supply values during the creation phase. Design the table in SSMS and check the column properties you will see (1, 1) under the ID column specification. This is what Sp_help displays

DBCC CHECKIDENT shows the current value used!

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉