关于重置自增列

[Fishest]

IF OBJECT_ID('TEST3') IS NOT NULL DROP TABLE TEST3
CREATE TABLE TEST3
(
ID INT IDENTITY(1,1) NOT NULL,
INFO VARCHAR(20) NOT NULL
)
INSERT INTO TEST3(INFO)
SELECT 'A1' UNION ALL
SELECT 'A2' UNION ALL
SELECT 'A3' UNION ALL
SELECT 'A4'

查询结果：
/*
  ID       INFO
------    ------
   1         A1
   2         A2
   3         A3
   4         A4
*/

现在我这样操作：

DELETE FROM TEST3 WHERE ID = 2

查询结果：
/*
  ID       INFO
------    ------
   1         A1
   3         A3
   4         A4
*/

假如我现在要插入一条数据，那他将会出现以下结果：
/*
ID INFO
------ ------
1 A1
3 A3
4 A4
5 A5
*/

有什么办法能让ID重新排列或者插入的时候让它自动填充2？？？？？？

[--小F--]

利用触发器实现标识列连续。(支持批量插入) 
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]
GO

ALTER TABLE [dbo].[ttt] ADD 
    CONSTRAINT [PK_ttt] PRIMARY KEY  CLUSTERED 
    (
        [id]
    )  ON [PRIMARY] 
GO

insert 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

[rucypli]

重新建个表 导进去 然后删原表，改表名

[Fishest]

还有别的解决方案吗？？？
据说有个DBCC的关键字，但是用不来。

[「已注销」]

--- 删除原表数据,并重置自增列
truncate table tablename  --truncate方式也可以重置自增字段
--重置表的自增字段,保留数据
DBCC CHECKIDENT (tablename,reseed,0) 
-- 设置允许显式插入自增列
SET IDENTITY_INSERT tablename  ON
-- 当然插入完毕记得要设置不允许显式插入自增列
SET IDENTITY_INSERT tablename  OFF


1、如果仅仅是指定值插入，可用以下语句，临时取消

SET IDENTITY_INSERT TableName ON
INSERT INTO tableName(xx,xx) values(xx,xx)
SET IDENTITY_INSERT TableName OFF

2、新增一列，删除自增列，修改改列名

alter table a add xxx int
update a set xxx=id
alter table a drop column id
exec sp_rename 'xxx', 'id', 'column'

3、通过修改系统关于该表的列属性，该方法使用不当将可能引起其它不可预料的错误

sp_configure 'allow update',1
reconfigure with override
go
update syscolumns set colstat=0 where colstat=1 and id=object_id('tablename')
go
sp_configure 'allow update',0
reconfigure with override

[Mr_Nice]

[Quote=引用楼主 fishest 的回复:]
SQL code

IF OBJECT_ID('TEST3') IS NOT NULL DROP TABLE TEST3
CREATE TABLE TEST3
(
ID INT IDENTITY(1,1) NOT NULL,
INFO VARCHAR(20) NOT NULL
)
INSERT INTO TEST3(INFO)
SELECT 'A1' UNION ALL
SELECT 'A2' ……
[/Quote]

删除重建，或者是重新填充都可以解决LZ的问题。
不过，断号的identity可以反映出一些业务操作问题。
LZ可以参考看看dawugui的方法来给出新的连续号。旧的断号正好可以去进行监测。

参考！

[htl258_Tony]

建议在查询的时候作下处理，如果一定要，参考：http://blog.csdn.net/htl258/archive/2009/07/22/4369943.aspx

[dawugui]

我认为还不如不用自增列。


用如下的方法：

select isnull(max(id),0) + 1 from tb

[Fishest]

看了大家的回复有点清楚了