Issues with DCBB CHECKIDNT

Question

Issues with DCBB CHECKIDNT

Rookee DBA

SSCrazy

Points: 2188
More actions
February 24, 2009 at 5:17 pm

#200219

I have a script which I should be able to execute multiple times with out any errors my script has the following stat
delete from sometablename where ID IN (1,2,3,4);
DBCC CHECKIDENT('sometablename', RESEED,0);
go
insert into sometablename values(1);
insert into sometablename values(2);
insert into sometablename values(3);
insert into sometablename values(4);
when I execute the script, I am getting issues with seed being set to 0. could some one let me know, how to sort the same?
cheers
Siddarth

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

your command reset the Id to zero...if you want, you could DBCC CHECKIDENT('sometablename', RESEED,-999); and the identi would start at -999, and work it's way towards zero and beyond

I assume you want it to restart at 1, so just change your command to DBCC CHECKIDENT('sometablename', RESEED,1);

the only other issue i think you might trip over is how the identity property works...if your table has more than 4 rows in it(you only deleted 4 rows in your example, not ALL rows), the ident will always be +1 greater than the max key currently in the table, no matter what.

this is to prevent issues like the identity being fine for id's 1,2,3,4. but crashing when it tries to insert 5.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Rookee DBA SSCrazy Points: 2188 More actions · Answer 2

If I set the SEED to 1, the Indentity column will have the value of 2, please correcat me if I am wrong.

cheers

Lowell SSC Guru Points: 323518 More actions · Answer 3

yeah, you are mistaken. the reseed value assigns the next value, not next+1.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Rookee DBA SSCrazy Points: 2188 More actions · Answer 4

I have tested the same by setting to 1 but seems i made a mistake somewhere and didnt got desired result.

thanks for the same.

cheers

Lowell SSC Guru Points: 323518 More actions · Answer 5

what result are you getting?

create table #test(

testid int identity(1,1) not null primary key,

stuff varchar(30) )

insert into #test

select 'one' union all

select 'two' union all

select 'three' union all

select 'four'

select * from #test --1,2,3,4

delete from #test

insert into #test

select 'this will be five'

select * from #test

delete from #test

DBCC CHECKIDENT('#test', RESEED,1);

insert into #test

select 'sixth insert is ident 1'

select * from #test

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!