Drop table #tb; Create table #tb(ch char(1),id int identity(1,1)); Insert Into #tb(ch) values('a'),('b'),('c'); Delete #tb Where id = 2; Go ----先查出不连续的最小值 Declare @MinLostNum as int; Set @MinLostNum = (Select top 1 id From #tb as t1 Where Not Exists( Select * From #tb as t2 Where t2.id = t1.id + 1)) Set IDENTITY_INSERT #tb On Insert Into #tb(ch,id) Values('D',@MinLostNum+1); Set IDENTITY_INSERT #tb Off Select * From #tb ------------------- a 1 D 2 c 3
Create table #tb(ch char(1),id int identity(1,1));
Insert Into #tb(ch) values('a'),('b'),('c');
Delete #tb Where id = 2;
Go
----先查出不连续的最小值
Declare @MinLostNum as int;
Set @MinLostNum = (Select top 1 id
From #tb as t1
Where Not Exists(
Select *
From #tb as t2
Where t2.id = t1.id + 1))
Set IDENTITY_INSERT #tb On
Insert Into #tb(ch,id) Values('D',@MinLostNum+1);
Set IDENTITY_INSERT #tb Off
Select * From #tb
-------------------
a 1
D 2
c 3