Automatic growth of string Fields

This problem occurs during learning.
The database has a number field.
Bh00001
Bh00002
Bh00003
Bh00004
How to achieve automatic Growth

 

-- The following code generates an 8-digit serial number. The serial number starts with BH, and the remaining 6 digits are serial numbers.
-- Get the function with the new number
Create Function f_nextbh ()
Returns char (8)
As
Begin
Return (select 'bh '+ right (1000001 + isnull (right (max (BH), 6), 0), 6) from TB with (xlock, paglock ))
End
Go

-- Apply a function to a table
Create Table Tb (
BH char (8) primary key default DBO. f_nextbh (),
Col INT)

-- Insert data
Begin tran
Insert Tb (COL) values (1)
Insert Tb (COL) values (2)
Insert Tb (COL) values (3)
Delete TB where Col = 3
Insert Tb (COL) values (4)
Insert Tb (BH, col) values (DBO. f_nextbh (), 14)
Commit tran

-- Display Results
Select * from TB
/* -- Result
BH col
---------------------------
Bh120001 1
Bh120002 2
Bh120003 4
Bh000004 14
--*/

 

Create Table TB
(ID int identity,
Name varchar (10 ),
Code as 'bh '+ right ('000000' + Cast (ID as varchar), 5 ))
Go
Insert Tb (name) Select 'A'
Union all select 'B'
Union all select 'C'
Union all select 'D'

Select * from TB

Drop table TB

/*
ID name Code
---------------------------------
1 A bh00001
2 B bh00002
3 C bh00003
4 d bh00004

(The number of affected rows is 4)
*/