标识列所用的计数值重置

标识列所用的计数值重置

　　　　　　　　　　　　　　　　　　　　　　　　　　　　flystone 整理

首先说两种可以重置种子的方法：

--
-- a:
TRUNCATE TABLE name
--新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。
/*
TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。
但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。

DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。
TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。
新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。
如果要删除表定义及其数据，请使用 DROP TABLE 语句。

对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。
由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。

TRUNCATE TABLE 不能用于参与了索引视图的表。

*/
-- b:
DBCC CHECKIDENT (表, RESEED, 1)
/*

DBCC CHECKIDENT说明

DBCC CHECKIDENT ('table_name', NORESEED)	不重置当前标识值。DBCC CHECKIDENT 返回一个报表，它指明当前标识值和应有的标识值。
DBCC CHECKIDENT ('table_name') 或DBCC CHECKIDENT ('table_name', RESEED)	如果表的当前标识值小于列中存储的最大标识值，则使用标识列中的最大值对其进行重置。
DBCC CHECKIDENT ('table_name', RESEED,new_reseed_value)	当前值设置为 new_reseed_value。如果自创建表后没有将行插入该表，则在执行 DBCC CHECKIDENT 后插入的第一行将使用new_reseed_value 作为标识。否则，下一个插入的行将使用 new_reseed_value + 1。如果 new_reseed_value 的值小于标识列中的最大值，以后引用该表时将产生 2627 号错误信息。

由此我想起上次有一个网友的问题能很好的让大家认识这个问题，

网友问题如下：

--有张表
Create table A(
A1 int identity(1,1) primary key,
A2 varchar(20),
A3 decimal(15,10)
)
--有个存储过程
Create proc deletes(@nA int)
as
begin tran
delete A where A1=@nA
if(@@Error=0)
Commit transaction
else
Rollback transaction
go
--当执行完
exec deletes 1
--以后
--我想让表里的所有记录A1从新从1开始排列,该怎么修改存储过程deletes,请高手指点

很显然在不利用SET IDENTITY_INSERT 开关项的前提下是无法在自增列完成这个功能的。

很自然的我们就引出这个SET IDENTITY_INSERT 开关项

SET IDENTITY_INSERT

允许将显式值插入表的标识列中。

语法

SET IDENTITY_INSERT [ database.[ owner.] ] { table } { ON | OFF }

好下面我们就先利用这个开发项完成　这个网友的功能　

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 2

select * from tb

drop proc deletes
drop table tb

结果一：

id col
----------- ----
1 a
2 b
3 c

结果二：

id col
----------- ----
1 a
2 c

（所影响的行数为 2 行）

到此以为完全实现这个网友的功能了，可是发现在调用exec deletes 　2　删除第二记录后，

马上进行一条的记录的插入时发种子值仍是在原来的基础上增加了，比如我们一共三记录，删除第二条后经重新整理以为再插入时id 应该是3,　可是发现不对，测试删除重新整理后查看@@identity值仍是3，显然这是达到不预想的效果　的.

insert tb select 'd'
select * from tb

/*

id col
----------- ----
1 a
2 c

4 d -----显然这儿是不理想的，

*/

那怎么办呢，这个时我们想起前面讲的重置种子值的方法：dbcc CHECKIDENT,

哈哈，所以我们有了下面的比较完美的方法：

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
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 2



select * from tb
insert tb select 'd' ---- add data again

select * from tb
select @@IDENTITY

insert tb select 'd'
select * from tb

/*

id col
----------- ----
1 a
2 c

3 d -----显然这儿是理想的结果，

*/

drop proc deletes
drop table tb

至此发现完全　满足这个朋友的预想目的。