如何创建这样的主键ID？

[weeksun23]

1.从1递增,递增量为1；
2.每从表中删除记录，主键就会重新排序
如：有如下表
ID name
1 a
2 b
3 c
4 d
5 e
当删除了第2行 第4行记录后，主键重新排序
变成
ID name
1 a
2 c
3 e

这样应如何设计？？？

[喜-喜]

没办法直接创建这样的字段！好像只能通过触发器或存储过程来实现相似的效果...

[weeksun23]

那应该如何通过触发器或储存过程来实现呢？？

[Shawn]

--可以用触发器, 但楼主这样做是没有什么意义的.可用其它方法达到你的需求。

if OBJECT_ID('tempdb..#temp') is not null
	drop table #temp
create table #temp
(
	id int,
	name varchar(10)
)
insert #temp
select 1, 'a' union all
select 3, 'c' union all
select 5, 'e'
go
--SQL:
;WITH cte as
(
	select rowno = ROW_NUMBER() over(order by id), * from #temp
)
update cte
set id = rowno
--result
select * from #temp
/*
id	name
1	a
2	c
3	e
*/

[insus]

可以参考此方法：
http://www.cnblogs.com/insus/articles/1922815.html

[--小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