--不連續也沒有影響阿 --如果需要連續序號,查詢的時候生成不就可以了? --2000 select ID=(select count(*) from tb where id<=t.id), * from tb t --2005 select ID=(row_number()over(order by (select 1))), * from tb --如果一定變動 可以用觸發器 create trigger tir_del on tb for delete as begin declare @i int set @i=0 update tb set id=@i,@i=@i+1 end
--创建测试表 CREATE TABLE t1(ID int IDENTITY,A int) GO --插入记录 INSERT t1 VALUES(1) GO--1. 将IDENTITY(标识)列变为普通列 ALTER TABLE t1 ADD ID_temp int GOUPDATE t1 SET ID_temp=ID ALTER TABLE t1 DROP COLUMN ID EXEC sp_rename N't1.ID_temp',N'ID',N'COLUMN' INSERT t1 VALUES(100,9) GO--2. 将普通列变为标识列 CREATE TABLE t1_temp(ID int,A int IDENTITY) SET IDENTITY_INSERT t1_temp ON INSERT t1_temp(ID,A) SELECT * FROM t1 SET IDENTITY_INSERT t1_temp OFF DROP TABLE T1 GOEXEC sp_rename N't1_temp',N't1' INSERT t1 VALUES(109999) GO--显示处理结果 SELECT * FROM t1 /*--结果: ID A ----------------- ----------- 1 1 100 9 109999 10 --*/
--> 测试数据: tb if object_id('tb') is not null drop table tb go create table tb (co1 int,n int)insert into tb select 1,(select count(*) from tb) insert into tb select 1,(select count(*) from tb) select * from tbco1 n ----------- ----------- 1 0 1 1(2 行受影响)
如果非要连续就可以先删除自增列 再建立 如果不需要连续的话 就直接在查询的时候做 --2000 select ID=(select count(*) from tb where id<=t.id), * from tb t --2005 select ID=(row_number()over(order by (select 1))), * from tb
同意,直接用UPDATE TB SET ID=@I,@i=ISNULL(@i,0)+1 其实查询时处理方便
--如果需要連續序號,查詢的時候生成不就可以了?
--2000
select
ID=(select count(*) from tb where id<=t.id),
*
from tb t
--2005
select
ID=(row_number()over(order by (select 1))),
*
from tb
--如果一定變動
可以用觸發器
create trigger tir_del on tb
for delete
as
begin
declare @i int
set @i=0
update tb set id=@i,@i=@i+1
end
CREATE TABLE t1(ID int IDENTITY,A int)
GO
--插入记录
INSERT t1 VALUES(1)
GO--1. 将IDENTITY(标识)列变为普通列
ALTER TABLE t1 ADD ID_temp int
GOUPDATE t1 SET ID_temp=ID
ALTER TABLE t1 DROP COLUMN ID
EXEC sp_rename N't1.ID_temp',N'ID',N'COLUMN'
INSERT t1 VALUES(100,9)
GO--2. 将普通列变为标识列
CREATE TABLE t1_temp(ID int,A int IDENTITY)
SET IDENTITY_INSERT t1_temp ON
INSERT t1_temp(ID,A) SELECT * FROM t1
SET IDENTITY_INSERT t1_temp OFF
DROP TABLE T1
GOEXEC sp_rename N't1_temp',N't1'
INSERT t1 VALUES(109999)
GO--显示处理结果
SELECT * FROM t1
/*--结果:
ID A
----------------- -----------
1 1
100 9
109999 10
--*/
if object_id('tb') is not null drop table tb
go
create table tb (co1 int,n int)insert into tb
select 1,(select count(*) from tb) insert into tb
select 1,(select count(*) from tb) select * from tbco1 n
----------- -----------
1 0
1 1(2 行受影响)
如果不需要连续的话
就直接在查询的时候做
--2000
select
ID=(select count(*) from tb where id<=t.id),
*
from tb t
--2005
select
ID=(row_number()over(order by (select 1))),
*
from tb
其实查询时处理方便