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...
目前在某些操作上，我必须删除旧数据并插入新数据。但是我注意到，尽管删除了数据，标识列并没有重置，而是从其最后的最大值继续。因此，我使用DBCC CheckIdent实现了相同的目标，所有这些都发生在trasaction中。我可以将事务回滚回初始状态吗?DBCC检查会产生什么问题吗?请指导……
1 个解决方案
#1
6
The test code below shows that the DBCC action can be rolled back:
下面的测试代码显示DBCC操作可以回滚:
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)
#1
6
The test code below shows that the DBCC action can be rolled back:
下面的测试代码显示DBCC操作可以回滚:
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)