SQL SERVER 如何處理帶字母的自增列–【葉子】

--需求說明:/*id         col---------- ----------AB00001    aAB00002    b--當再插入資料的時候讓id自動變成AB00003*/--1.求最大值法（高並發時不適用，只是介紹個思路）--測試資料if object_id('[macotb]') is not null drop table [macotb]create table [macotb] (id varchar(7),col varchar(1))insert into [macotb]select 'AB00001','a' union allselect 'AB00002','b'declare @max varchar(7)select @max='AB'+right('00000'+ltrim(max(replace(id,'AB','')+1)),5) from [macotb]insert into [macotb] select @max,'c'select * from [macotb]/*id      col------- ----AB00001 aAB00002 bAB00003 c*/--2.利用@@identity，分步處理if object_id('[macotb]') is not null drop table [macotb]create table [macotb] ([no] int identity,id varchar(7),col varchar(1))insert into [macotb]select 'AB00001','a' union allselect 'AB00002','b'insert into [macotb](col) select 'c'update [macotb] set id='AB'+right('00000'+ltrim([no]),5) where [no]=@@identityselect id,col from [macotb]/*id      col------- ----AB00001 aAB00002 bAB00003 c*/--3.直接添加運算列if object_id('[macotb]') is not null drop table [macotb]create table [macotb] ([no] int identity,id as ('AB'+right('00000'+ltrim([no]),5)),col varchar(1))insert into [macotb](col) select 'a' union all select 'b'select id,col from [macotb]/*id           col------------ ----AB00001      aAB00002      b*/insert into [macotb](col) select 'c' union all select 'd'select id,col from [macotb]/*id           col------------ ----AB00001      aAB00002      bAB00003      cAB00004      d*/--葉子建議使用第三種方式！