identity seed

Question

identity seed

sqlfriend

SSC Guru

Points: 52781
More actions
October 3, 2012 at 3:53 pm

#265389

I have a question about identity seed.
We have a table that has 4300 records with a surrogate key 1-4300.
Now for some reason we would like to reseed the table, starting from 5000, identity seed is 1.
when I insert records I see it start from 5001, instead of 5000.
But if I import in an empty table with the same identity settings for dev enviroment, it starts from 5000.
Why is that? Thanks,

Viewing 9 posts - 1 through 8 (of 8 total)

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

sql-lover SSCoach Points: 18530 More actions · Answer 1

I can be wrong, but if you want to start from where you left, different table, you must use SET IDENTITY INSERT ON ... insert, then turned it off. That way, you will preserve current order while inserting on your dev table.

sqlfriend SSC Guru Points: 52781 More actions · Answer 2

Thanks,

but I mean the starting seed number, I mean in order for it start again from 5000 in a table that already have records, it seems I need to setup the seed to 4999 instead of 5000

sql-lover SSCoach Points: 18530 More actions · Answer 3

sqlfriends (10/3/2012)
Thanks,
but I mean the starting seed number, I mean in order for it start again from 5000 in a table that already have records, it seems I need to setup the seed to 4999 instead of 5000

Use SET IDENTITY INSERT command... insert data from old table ... set it off ...

Then use DBCC command (dev table) and set it to start from the new seed you need. Of course, assuming it is not violating the existing records and most recent or last seed.

DBCC CHECKIDENT (yourtable, reseed, 4999)

Next value, will be 5000, I think, assuming you have no record with that value.

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 4

sql-lover (10/3/2012)
sqlfriends (10/3/2012)
Thanks,
but I mean the starting seed number, I mean in order for it start again from 5000 in a table that already have records, it seems I need to setup the seed to 4999 instead of 5000
Use SET IDENTITY INSERT command... insert data from old table ... set it off ...
Then use DBCC command (dev table) and set it to start from the new seed you need. Of course, assuming it is not violating the existing records and most recent or last seed.
DBCC CHECKIDENT (yourtable, reseed, 4999)
Next value, will be 5000, I think, assuming you have no record with that value.

Which old table? There's no old table required. The OP just wants to reseed the current table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 5

sqlfriends (10/3/2012)
I have a question about identity seed.
We have a table that has 4300 records with a surrogate key 1-4300.
Now for some reason we would like to reseed the table, starting from 5000, identity seed is 1.
when I insert records I see it start from 5001, instead of 5000.
But if I import in an empty table with the same identity settings for dev enviroment, it starts from 5000.
Why is that? Thanks,

The answer is because that's the way it's designed. A quick trip to Books Online (the free help system that comes with SQL Server) explains it under the entry for DBCC CHECKIDENT...

DBCC CHECKIDENT ( 'table_name', RESEED, new_reseed_value )
Current identity value is set to the new_reseed_value. If no rows have been inserted to the table since it was created, the first row inserted after you run DBCC CHECKIDENT uses new_reseed_value as the identity. Otherwise, the next row inserted uses new_reseed_value + 1. If the value of new_reseed_value is less than the maximum value in the identity column and a PRIMARY KEY or UNIQUE constraint exists on the identity column, error message 2627 will be generated on subsequent references to the table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sql-lover SSCoach Points: 18530 More actions · Answer 6

Jeff Moden (10/3/2012)
sql-lover (10/3/2012)
sqlfriends (10/3/2012)
Thanks,
but I mean the starting seed number, I mean in order for it start again from 5000 in a table that already have records, it seems I need to setup the seed to 4999 instead of 5000
Use SET IDENTITY INSERT command... insert data from old table ... set it off ...
Then use DBCC command (dev table) and set it to start from the new seed you need. Of course, assuming it is not violating the existing records and most recent or last seed.
DBCC CHECKIDENT (yourtable, reseed, 4999)
Next value, will be 5000, I think, assuming you have no record with that value.
Which old table? There's no old table required. The OP just wants to reseed the current table.

This...

"But if I import in an empty table with the same identity settings for dev enviroment, it starts from 5000."

I never said a second or old table is required.

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 7

Ah... understood and thanks for the feedback. The OP was just using that to confirm the confusion about what DBCC CHECKINDENT would do.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqlfriend SSC Guru Points: 52781 More actions · Answer 8

Thanks all.

Jeff answered my question just on the point, but sql-lover's answer help me too.