一般情况下,没有必要追求这个标识列一定要连续 如果要使用行号,可以在查询中处理如果一定要把它改为连续的,可以通过一个临时表来处理select id=identity(int,1,1),col2,col3,... into # from tb --col2,col3,...是除了标识列以外的列truncate table tbselect * into tb from #drop table #
;with Args ( select *,row_number() over (order by id) as Rownum from tb )update tb set keycol = Rownum
truncate table tablename
select col1,col2,col3.....into #tb from tb truncate table tb insert into tb select identity(int ,1,1),* from #tb
declare @t table(id int identity ,[name] varchar(10)) insert @t select 'A' union all select 'B' union all select 'C' union all select 'D' delete @t where id=2 --2000 select id=(select count(*) from @t where id<=t.id),[name] from @t t --2005 select id=row_number() over(order by id),[name] from @t /*id name ----------- ---------- 1 A 2 C 3 D(3 行受影响)id name -------------------- ---------- 1 A 2 C 3 D(3 行受影响) */
如果要使用行号,可以在查询中处理如果一定要把它改为连续的,可以通过一个临时表来处理select id=identity(int,1,1),col2,col3,... into # from tb
--col2,col3,...是除了标识列以外的列truncate table tbselect * into tb from #drop table #
(
select *,row_number() over (order by id) as Rownum
from tb
)update tb
set keycol = Rownum
truncate table tb
insert into tb select identity(int ,1,1),* from #tb
insert @t select 'A' union all
select 'B' union all
select 'C' union all
select 'D'
delete @t where id=2
--2000
select id=(select count(*) from @t where id<=t.id),[name] from @t t
--2005
select id=row_number() over(order by id),[name] from @t
/*id name
----------- ----------
1 A
2 C
3 D(3 行受影响)id name
-------------------- ----------
1 A
2 C
3 D(3 行受影响)
*/