流水號自動補號和計算所缺最小號和最大號

--自已做標識列的例子,不自動重排編號,而是自動補號:

--創建得到最大id的函數
create function f_getid()
returns char(3)
as
begin
declare @id int

if not exists(select 1 from tb where id='001')
  set @id=1
else
begin
  select @id=max(id) from tb
  if @id is null
    set @id=1
  else
    begin
      declare @id1 int
      select @id1=min(id) from tb a where id<>@id and not exists(select 1 from tb where id=a.id+1)
      if @id1 is not null set @id=@id1
      set @id=@id+1
    end
end
return(right('000'+cast(@id as varchar),3))
end
go

--創建表
create table tb(id char(3) primary key default dbo.f_getid(),name varchar(10))
go


--插入記錄測試
insert into tb(name) values('張三')
insert into tb(name) values('張四')
insert into tb(name) values('張五')
insert into tb(name) values('張六')
insert into tb(name) values('張七')
insert into tb(name) values('張八')
insert into tb(name) values('張九')
insert into tb(name) values('張十')

--顯示插入的結果
select * from tb

--刪除部分記錄
delete from tb where name in('張三','張七','張八','張十')

--顯示刪除後的結果
select * from tb

--再次插入記錄
insert into tb(name) values('李一')
insert into tb(name) values('李二')

--顯示插入的結果
select * from tb order by id
go

--刪除環境
drop table tb
drop function f_getid

/*--測試結果
id   name       
---- ---------- 
001  李一
002  張四
003  張五
004  張六
005  李二
007  張九

（所影響的行數為 6 行）
--*/

另外一种計算缺號的算法
---最小
--select min(b+1) as lostnum from a where (not ((b+1) in (select b from a)))
select min(a+1) as lostnum from t where (not ((a+1) in (select a from t)))

---最大
--select max(b-1) as lostnum from a where (not ((b-1) in (select b from a)))
select max(a-1) as lostnum from t where (not ((a-1) in (select a from t)))