SQL sequential account generation

1 -- The followingCodeGenerate an 8-digit serial number. The serial number starts with BH and the remaining six digits are serial numbers.
2 -- Function with the new number
3 Create   Function F_nextbh ()
4 Returns   Char ( 8 )
5 As
6 Begin
7 Return ( Select   ' BH ' + Right ( 1000001 + Isnull ( Right ( Max (BH ), 6 ), 0 ), 6 ) From TB With (Xlock, paglock ))
8 End
9 Go
10
11 -- Apply functions in the table
12 Create   Table TB (
13 BH Char ( 8 ) Primary   Key   Default DBO. f_nextbh (),
14 Col Int )
15
16 -- Insert data
17 Begin   Tran
18 Insert TB (COL) Values ( 1 )
19 Insert TB (COL) Values ( 2 )
20 Insert TB (COL) Values ( 3 )
21 Delete TB Where Col = 3
22 Insert TB (COL) Values ( 4 )
23 Insert TB (BH, col) Values (DBO. f_nextbh (), 14 )
24 Commit   Tran
25
26 -- Display result
27 Select   *   From TB
28 /* -- Result
29 BH col
30 ---------------------------
31 Bh120001 1
32 Bh120002 2
33 Bh120003 4
34 Bh000004 14
35 -- */