create table tb(id int identity(1,1),c2 varchar(10)) insert into tb(c2) select 'abc' union all select 'def' union all select 'hij' union all select 'klm' union all select 'xyz' go delete from tb where id=4 select * from tb /* id c2 ----------- ---------- 1 abc 2 def 3 hij 5 xyz(4 行受影响) */ go --下面开始处理 select * into # from tb truncate table tb insert into tb(c2) select c2 from # order by id select * from tb /* id c2 ----------- ---------- 1 abc 2 def 3 hij 4 xyz(4 行受影响)*/ go drop table tb
insert into tb(c2) select 'abc'
union all select 'def'
union all select 'hij'
union all select 'klm'
union all select 'xyz'
go
delete from tb where id=4
select * from tb
/*
id c2
----------- ----------
1 abc
2 def
3 hij
5 xyz(4 行受影响)
*/
go
--下面开始处理
select * into # from tb
truncate table tb
insert into tb(c2)
select c2 from # order by id
select * from tb
/*
id c2
----------- ----------
1 abc
2 def
3 hij
4 xyz(4 行受影响)*/
go
drop table tb
--reset the identity values肯定管用,我试过的,不过你要先找出断层的值然后再重设。