删除记录后，怎么让序号连续呢_Sql Server

删除记录后，如何让序号连续呢？
用vb.net删除或插入access2010数据库里的记录后，自动编号的列"xh"不能相连，有朋友提示：“插入或删除后，执行Drop子句，删除自动编号字段，再执行Alter子句，增加自动编号字段，即可实现”

如何写运用drop、alter语句的代码使得xh相连呢？

------解决方案--------------------

SQL code

利用触发器实现标识列连续。(支持批量插入) 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.7802   peter   2008-12-15 17:36:37.7803   man 2008-12-15 17:36:37.7804   lida    2008-12-15 17:36:37.7805   fcuandy 2008-12-15 17:36:37.793*/GOCREATE TRIGGER tr ON tttINSTEAD OF INSERTAS    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    GODELETE FROM ttt WHERE name = 'peter' OR name='lida'GOINSERT ttt SELECT 'xxx',getdate()INSERT ttt SELECT 'yyy',GETDATE()GOSELECT * FROM ttt/*1   logan   2008-12-15 17:37:20.9672   xxx 2008-12-15 17:37:21.0133   man 2008-12-15 17:37:20.9674   yyy 2008-12-15 17:37:21.0305   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.4502   roy_88  2008-12-15 17:38:29.5303   man 2008-12-15 17:38:29.4674   limpire 2008-12-15 17:38:29.5305   fcuandy 2008-12-15 17:38:29.4676   熊   2008-12-15 17:38:29.530*/本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/fcuandy/archive/2008/12/15/3522876.aspx
------解决方案--------------------
执行这样的查询语句:
SQL codeselect xh=row_number()over(order by id),* from pl