求教sqlserver自增长字段如何清零 或者自动填补空缺？

Question

如题 因为中间好多字段是被删除以后空缺的。。而且很多都是我试验的时候用过的 能让他自动归零重新开始计算吗 或者让他自动填补空缺字段

Answer 1

利用触发器实现标识列连续。(支持批量插入) 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ttt]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ttt]
GO/****** Object:  Table [dbo].[ttt]    Script Date: 2008-12-15 17:11:26 ******/
CREATE TABLE [dbo].[ttt] (
    [id] [int] IDENTITY (1, 1) NOT NULL ,
    [name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
    [time] [datetime] NULL 
) ON [PRIMARY]
GOALTER TABLE [dbo].[ttt] ADD 
    CONSTRAINT [PK_ttt] PRIMARY KEY  CLUSTERED 
    (
        [id]
    )  ON [PRIMARY] 
GOinsert into ttt(name,time) values('logan',getdate());
insert into ttt(name,time) values('peter',getdate());
insert into ttt(name,time) values('man',getdate());
insert into ttt(name,time) values('lida',getdate());
insert into ttt(name,time) values('fcuandy',getdate());select * from ttt
/*
1   logan   2008-12-15 17:36:37.780
2   peter   2008-12-15 17:36:37.780
3   man 2008-12-15 17:36:37.780
4   lida    2008-12-15 17:36:37.780
5   fcuandy 2008-12-15 17:36:37.793
*/
GO
CREATE TRIGGER tr ON ttt
INSTEAD OF INSERT
AS
    SET IDENTITY_INSERT ttt ON
    DECLARE @n INT
    SELECT @n=MAX(id) FROM ttt
    ;WITH fc AS
    (
        SELECT n=1
        UNION ALL
        SELECT nn=n+1 FROM fc WHERE n<@n
    ),fc1 AS
    (
    SELECT n FROM fc a
    LEFT JOIN ttt b
        ON a.n = b.id
        WHERE b.id IS NULL
    )
    INSERT ttt(id,name,time) SELECT n,name,time
        FROM
        (SELECT ROW_NUMBER() OVER (ORDER BY GETDATE()) idx,n FROM fc1) a
    INNER JOIN
        (
        SELECT idx=ROW_NUMBER() OVER (ORDER BY GETDATE()),name,time FROM inserted
        ) b
        ON a.idx=b.idx
    DECLARE @r INT
    SELECT @r=@@ROWCOUNT
    
    SET IDENTITY_INSERT ttt OFF
        INSERT ttt(name,time) SELECT name,time FROM 
            (
                SELECT idx=ROW_NUMBER() OVER (ORDER BY GETDATE()),name,time FROM inserted
            ) x
            WHERE idx>@r
    GO
DELETE FROM ttt WHERE name = 'peter' OR name='lida'
GO
INSERT ttt SELECT 'xxx',getdate()
INSERT ttt SELECT 'yyy',GETDATE()
GO
SELECT * FROM ttt
/*
1   logan   2008-12-15 17:37:20.967
2   xxx 2008-12-15 17:37:21.013
3   man 2008-12-15 17:37:20.967
4   yyy 2008-12-15 17:37:21.030
5   fcuandy 2008-12-15 17:37:20.967
*/DELETE FROM ttt WHERE name ='xxx' OR name='yyy'INSERT ttt SELECT 'roy_88',GETDATE() UNION ALL SELECT 'limpire',GETDATE() UNION ALL SELECT '熊',GETDATE()SELECT * FROM ttt/*
1   logan   2008-12-15 17:38:29.450
2   roy_88  2008-12-15 17:38:29.530
3   man 2008-12-15 17:38:29.467
4   limpire 2008-12-15 17:38:29.530
5   fcuandy 2008-12-15 17:38:29.467
6   熊   2008-12-15 17:38:29.530
*/
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/fcuandy/archive/2008/12/15/3522876.aspx

Answer 2

--------------------------------------------------------------------------------------------------------------------------
------ 邹建原创 ------
--------------------------------------------------------------------------------------------------------------------------
      如果使用系统的identity，会出现这样一种情况：当删除了一条记录，再增加一条新记录时会导致新增记录的id是记录中最大id+1，这样在现存的记录中id就出现间断！所以，自己做一个标识列，目的就是弥补不足，出现上面情况时id自动更新，并连续。
      注意：标识列是不允许修改其值的，所以如果你要实现删除后自动重排，就不能用标识列，否则会带来很多麻烦。
      --创建得到最大id的函数
create function f_getid()
returns int
as
        begin
                    declare @id int
                    select  @id = max(id)  from  tb
                    set  @id = isnull(@id,0)+1
                    return(@id)
             end
go     --创建表
         create  table tb(id  int  default  dbo.f_getid() primary key,name varchar(10))
         go     --创建触发器,在删除表中的记录时,自动更新记录的id(**如果不要此功能,则删除此触发器)
       create trigger t_delete on tb
       AFTER delete
       as
           declare  @id int,@mid int
           select  @mid=min(id),@id=@mid-1  from deleted
           update  tb  set  id=@id,@id=@id+1  where id>@mid
       go     --插入记录测试
       insert into tb(name) values('张三')
       insert into tb(name) values('张四')
       insert into tb(name) values('张五')
       insert into tb(name) values('张六')
       insert into tb(name) values('张七')
       insert into tb(name) values('张八')
       insert into tb(name) values('张九')
       insert into tb(name) values('张十')     --显示插入的结果
       select * from tb     --删除部分记录
       delete from tb where name in('张五','张七','张八','张十')     --显示删除后的结果
       select * from tb     --删除环境
       drop table tb
       drop function f_getid/*--测试结果
id          name       
----------- ---------- 
1           张三
2           张四
3           张五
4           张六
5           张七
6           张八
7           张九
8           张十（所影响的行数为 8 行）id          name       
----------- ---------- 
1           张三
2           张四
3           张六
4           张九（所影响的行数为 4 行）
--*/

Answer 3

declare @i integer
set @i = 0update t set col1 =@i , @i=@i+1如果不是自增列的话，可以用上面的代码重排。自增列没有试过。你试试看可不可以。