标识列顺序排列问题

一、我自己测试的一个触发器，及供参考 

create trigger U_id on information for delete as declare @id int declare @maxid int select @id=id from deleted select @maxid =max(id) from information while @maxid>@id begin set @id=@id+1 set identity_insert information on insert into information(id,Sex) select @id-1 ,sex from information where id=@id delete from information where id=@id set identity_insert information off end  

 

二、更新标识列的方法可参考：SQL SERVER ID标识列重新排列

出处：http://blog.youkuaiyun.com/lenotang/archive/2008/10/24/3134773.aspx

 
大家经常使用SQL Server的ID自增长，也就是所谓的标识列来充当某张表的主键。这里有两个概念要澄清：
 

1. 标识列：可用 IDENTITY 属性建立，使应用程序开发人员得以对表中所插入的第一行指定标识数字（Identity Seed 属性），并确定要添加到种子上的增量（Identity Increment 属性）以决定后面的标识数字。在向具有标识符列的表中插入值时，通过递增种子值的方法自动生成下一个标识值。
 

2. 主键：可用primary key表示。唯一标识表中的所有行的一个列或一组列。主键不允许空值。不能存在具有相同的主键值的两个行，因此主键值总是唯一标识单个行。
 

       那么，当我们表中的数据很多，又有些删除的操作时，标识列就不连贯了，我们如何让表中已有记录的标识列从1开始重新排列，并将标识列的下一个值设为记录数加1呢？下面我们以一张student（id identity(1,1) primary key,name,age）表为例，给大家提供几种方法，以供参考：

 

(1) 清空表，标识种子置初值，这里要注意的是下面的语句会清空表中的所有原有数据。
truncate table student
 

(2) 先要取消标识列，完成下面的语句后再将标识列加上，这样标识列又可以按顺序递增了。这里你可以直接使用企业管理器操作。
declare   @i   int
select   @i   =   0
update   student set @i = @i+1,id = @i
 

(3) 利用临时表，步骤就是将表中除id以外的数据拷贝到临时表中，再清空原表，插入临时表的数据，最后删除临时表。
--将数据倒入临时表
select  name,age   into   #   from  student   order   by   id
--清空源表,它同时会重置标识列
truncate   table   student
--将数据导回源表
insert   into   student   select   *   from   #
--删除临时表 
drop   table   #
 

这里顺便提一下，有同志在插入测试数据的时候一条一条记录的插入，这样太浪费时间，我们可以写一条循环语句加快测试速度：
declare   @i   int  
  set   @i=1  
  while   @i<=100  
        begin  
            insert   into   student   values   ('tbag'+cast(@i as varchar)
,25+@i)  
            set   @i=@i+1  
        end

 

三、DBCC CheckIdent

出处：http://blog.youkuaiyun.com/Hopewell_Go/archive/2006/10/09/1327688.aspx

核对当前特定表的自动增量值，如果需要，可以纠正自动增量的值。
语法：
DBCC CHECKIDENT
    ( 'table_name'
        [ , { NORESEED
                | { RESEED [ , new_reseed_value ] }
            }
        ]
    )
参数：
‘tableName’是检查当前自动增量的表名。此表名必需是符合的标示符。此表必需包含自动增量字段。
Noreseed,当前特定自动增量的值不被纠正。
RESEED,当前特定自动增量的值被纠正。
New_reseed_value,重新设置自动增量字段的起初值.
注释
如有必要，DBCC CHECKIDENT 会更正列的当前标识值。然而，如果标识列是使用 NOT FOR REPLICATION 子句（在 CREATE TABLE 或 ALTER TABLE 语句中）创建的，则不更正当前标识值。如果标识列上有主键或唯一键约束，无效标识信息可能会导致错误信息 2627。对当前标识值所做的具体更正取决于参数规范。
DBCC CHECKIDENT statement Identity correction(s) made
DBCC CHECKIDENT ('table_name', NORESEED) 不重置当前标识值。DBCC CHECKIDENT 返回一个报表，它指明当前标识值和应有的标识值。
DBCC CHECKIDENT ('table_name') or 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 号错误信息

注：
当前标识值可以大于表中的最大值。在此情况下，DBCC CHECKIDENT 并不自动重置当前标识值。若要在当前标识值大于列中的最大值时对当前标识值进行重置，请使用两种方法中的任意一种：
 
一、执行 DBCC CHECKIDENT ('table_name', NORESEED) 以确定列中的当前最大值，然后使用 DBCC CHECKIDENT ('table_name', RESEED, new_reseed_value) 语句将该值指定为 new_reseed_value。
 
 
二、将 new_reseed_value 置为很小值来执行 DBCC CHECKIDENT ('table_name', RESEED, new_reseed_value)，然后运行 DBCC CHECKIDENT ('table_name', RESEED)。
例如：
有表city
ID name   type
1   1       a
2   2       b
3   3       c
4   1       a
5   2       b
6   3       c
7   1       a
8   2       b
9   3       c
10 1       a
11 2       b
12 3       c
此时当前identity最大值与ID最大值一样，都为12.
现在删除后面一部分数据，city变为
ID name   type
1   1       a
2   2       b
3   3       c
4   1       a
5   2       b
此时当前identity最大值与ID最大值I 一样，ID列最大值为5，identity还是为12.此时要调整过来，将identity值调整为5..具体操作如下：
运行DBCC CHECKIDENT ('city', RESEED, 1)，再运行DBCC CHECKIDENT ('table_name', RESEED)将其重值。
 
结果集
不管是否指定任何选项（针对于包含标识列的表；下例使用 pubs 数据库的 jobs 表），DBCC CHECKIDENT 返回以下结果集（值可能会有变化）：
 
Checking identity information: current identity value '14', current column value '14'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
 
权限
DBCC CHECKIDENT 权限默认授予表所有者、sysadmin 固定服务器角色和 db_owner 固定数据库角色的成员且不可转让。
 
示例
A. 如有必要，重置当前标识值
下例在必要的情况下重置 jobs 表的当前标识值。
 
USE pubs
GO
DBCC CHECKIDENT (jobs)
GO
 
B. 报告当前标识值
下例报告 jobs 表中的当前标识值；如果该标识值不正确，并不对其进行更正。
 
USE pubs
GO
DBCC CHECKIDENT (jobs, NORESEED)
GO
 
C. 强制当前标识值为 30
下例强制 jobs 表中的当前标识值为 30。
 
USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED, 30)
GO