How does SQL Server handle auto-increment columns with letters?

• -- Requirement Description:
• /*
• Id col
• --------------------
• Ab00001
• Ab00002 B
• -- When data is inserted, the ID is automatically changed to ab00003.
• */
• -- 1. Calculate the maximum value (this method is not applicable when high concurrency is used. It just introduces the idea)
• -- Test Data
• 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 ('000000' + ltrim (max (replace (ID, 'AB', '') + 1), 5) from [macotb]
• Insert into [macotb] Select @ Max, 'C'
• Select * from [macotb]
• /*
• Id col
• -----------
• Ab00001
• Ab00002 B
• Ab00003 C
• */
• -- 2. Use @ identity for step-by-step processing
• 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 ('000000' + ltrim ([No]), 5) where [No] = @ identity
• Select ID, Col from [macotb]
• /*
• Id col
• -----------
• Ab00001
• Ab00002 B
• Ab00003 C
• */
• -- 3. directly add operation Columns
• If object_id ('[macotb]') is not null
• Drop table [macotb]
• Create Table [macotb]
• (
• [No] int identity,
• ID as ('AB' + right ('000000' + ltrim ([No]), 5 )),
• Col varchar (1)
• )
• Insert into [macotb] (COL) Select 'A' Union all select 'B'
• Select ID, Col from [macotb]
• /*
• Id col
• ----------------
• Ab00001
• Ab00002 B
• */
• Insert into [macotb] (COL) Select 'C' Union all select 'D'
• Select ID, Col from [macotb]
• /*
• Id col
• ----------------
• Ab00001
• Ab00002 B
• Ab00003 C
• Ab00004 d
• */
• -- The third method is recommended!