CODEWITHSUNDEEP
sqlsql-serversql-server-2005transactions
HotTester
HotTester

Reputation: 5768

Can we rollback to original state after we have used DBCC CHECKIDENT to restart Identity column count?

Currently on some operations I have to delete the old data and insert new one. But I noticed that inspite of deleting data the identity column did not reset and continued from its last max value. So i used the DBCC CheckIdent for achieve the same all this is taking place within a trasaction. Can i rollback the transaction back to the intital state ? Would the DBCC CHECKIDENT pose any problems ? Kindly guide...

Upvotes: 4

Views: 1612

Answers (1)

Ed Harper
Ed Harper

Reputation: 21505

The test code below shows that the DBCC action can be rolled back:

create table #t
(id int identity, val1 int)
go

insert #t (val1)
values (1),(2),(3)

select MAX(id) AS before from #t

begin tran 

    delete #t

    dbcc checkident (#t, reseed,0)

    select MAX(id) AS inside_tran from #t   

rollback

select MAX(id) as after_rollback from #t
dbcc checkident (#t, noreseed)

Upvotes: 7

Related Questions