create table tbIdentity(ID int identity(1,1) primary key,Number int)
go
insert into tbIdentity
select 2
go
insert into tbIdentity --插入不成功,但Identity也自动增大
select 'BB'
Union all select 2
go
insert into tbIdentity --插入成功,但Identity值已经变位3
select 3
go
select * from tbIdentity drop table tbIdentity
go
insert into tbIdentity
select 2
go
insert into tbIdentity --插入不成功,但Identity也自动增大
select 'BB'
Union all select 2
go
insert into tbIdentity --插入成功,但Identity值已经变位3
select 3
go
select * from tbIdentity drop table tbIdentity
create table #(id int identity(0,1),a int)
insert # select 0 union all select 1--事务回滚
begin tran
insert # select 1
rollback traninsert # select 2
select * from #
drop table #
GO
/*--结果
id a
----------- -----------
0 0
1 1
3 2 --事务回滚一样导致id自增(所影响的行数为 3 行)
--*/
create table #(id int identity(0,1),a int)
insert # select 0 union all select 1 union all select 2--删除插入的最后一条
delete from # where a=2insert # select 2
select * from #
drop table #
GO
/*--结果
id a
----------- -----------
0 0
1 1
3 2 --删除后,id不回收(所影响的行数为 3 行)
--*/
--3.强制设置标识值
create table #(id int identity(0,1),a int)
insert # select 0 union all select 1--强制设置当前标识值
DBCC CHECKIDENT(#,RESEED,2)insert # select 2
select * from #
drop table #
GO
/*--结果id a
----------- -----------
0 0
1 1
3 2(所影响的行数为 3 行)
--*/
select a,b from oldtablename(a,b)選擇的字段除了自增字段外一定要相符