alter table A add col int identity(1,1)
go
alter table A drop column A1
go
exec sp_rename 'A.col','A1','column'--重命名
go
可在企业管理调整列的顺序
go
alter table A drop column A1
go
exec sp_rename 'A.col','A1','column'--重命名
go
可在企业管理调整列的顺序
create table tb(id int identity(1,1),col char(1))
insert tb select 'a'
insert tb select 'b'
insert tb select 'c'
go
select * from tb
go
Create proc deletes(@nA int)
as
begin tran SET IDENTITY_INSERT tb ON
delete from tb where id=@nA
if(@@Error=0)
begin
select * into # from tb where id >@nA
delete from tb where id > @na
insert tb(id,col) select id - 1 ,col from #
Commit transaction
drop table #
end
else
Rollback transaction
SET IDENTITY_INSERT tb OFF
go
exec deletes 2select * from tbdrop proc deletes
drop table tb
/*
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)id col
----------- ----
1 a
2 b
3 c(所影响的行数为 3 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)id col
----------- ----
1 a
2 c(所影响的行数为 2 行)
*/
insert tb select 'a'
insert tb select 'b'
insert tb select 'c'
go
select * from tb
go
Create proc deletes(@nA int)
as
declare @i int
begin tran SET IDENTITY_INSERT tb ON
delete from tb where id=@nA
if(@@Error=0)
begin
select * into # from tb where id >@nA
delete from tb where id > @na
insert tb(id,col) select id - 1 ,col from #
Commit transaction drop table #
end
else
Rollback transaction select @i = count(1) from tb
SET IDENTITY_INSERT tb OFF
DBCC CHECKIDENT ('tb',RESEED,@i)
go
exec deletes 2select * from tb
insert tb select 'd' ---- add data againselect * from tb
select @@IDENTITY
drop proc deletes
drop table tb
/*
(
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)id col
----------- ----
1 a
2 b
3 c(所影响的行数为 3 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)检查标识信息: 当前标识值 '3',当前列值 '2'。
DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。
id col
----------- ----
1 a
2 c(所影响的行数为 2 行)
(所影响的行数为 1 行)id col
----------- ----
1 a
2 c
3 d(所影响的行数为 3 行)
----------------------------------------
3(所影响的行数为 1 行)*/DBCC CHECKIDENT ('tb',RESEED,@i)
没这句在删除 后种子还是跳,加了以后没不会再有问题