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
*/

--           ！