T-SQL 一段没什么用的代码

2010-09-06 13:23 Nana's Lich 阅读(510) 评论(0) 编辑收藏举报

今天做了一些关于SQL Server的测试……折腾几圈下来之后自增量id就惨不忍睹了……于是东搜搜西查查拼凑出来这么一段东西，测试几次没发现严重问题，可以拿来重置表中的自增量id。

declare @p_tablename nvarchar(128);
set @p_tablename = 'Table_1';
 
 
declare @seed numeric(38),
        @incr numeric(38),
        @last numeric(38),
        @vmax numeric(38),
        @cname nvarchar(128),
        @sql  nvarchar(max);
declare @tmax table(cmax numeric(38));
 
 
select
    @seed = cast(seed_value as numeric(38)),
    @incr = cast(increment_value as numeric(38)),
    @last = cast(last_value as numeric(38)),
    @cname = "name"
from sys.identity_columns
where object_name(object_id) = @p_tablename;
 
set @sql = 'select max("' + @cname
  + '") from ' + quotename(@p_tablename);
insert into @tmax
exec(@sql);
select @vmax = cmax from @tmax;
 
set @vmax = case when @vmax is null
then @seed - @incr
else floor((@vmax - @seed) / @incr) * @incr + @seed
end;
 
dbcc checkident(@p_tablename, reseed, @vmax);

这段代码会寻找指定数据表的自增量id列，然后根据这列中存在的最大数值来设置合适的“下一个”。

我在设计这段代码的时候考虑了两种特殊情况：

一种是列中的最大数值和初始值、增量并不对齐的情况——在这种情况下它会使用最小的有效且对齐的自增量id；

另一种情况则是当表中所有记录都被清空的时候——在元数据中，新建的表的“上一个自增量”的值是NULL，也就是未初始化的意思，但在我的测试中，使用DBCC命令似乎不能设置NULL……所以这里使用了一个迂回的办法，就是用设计上的初始值减去增量，这样当插入下一条记录的时候新产生的自增量值正好是设计上的初始值。