SQL SERVER 如何处理带字母的自增列--【叶子】

--需求说明:
/*
id         col
---------- ----------
AB00001    a
AB00002    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 all
select '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 a
AB00002 b
AB00003 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 all
select 'AB00002','b'

insert into [macotb](col) select 'c'
update [macotb] 
set id='AB'+right('00000'+ltrim([no]),5) where [no]=@@identity

select id,col from [macotb]
/*
id      col
------- ----
AB00001 a
AB00002 b
AB00003 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      a
AB00002      b
*/

insert into [macotb](col) select 'c' union all select 'd'
select id,col from [macotb]
/*
id           col
------------ ----
AB00001      a
AB00002      b
AB00003      c
AB00004      d
*/

--叶子建议使用第三种方式！