Encounter this problem in study
There are numbered fields in the database
BH00001
BH00002
BH00003
BH00004
How to achieve automatic growth
--The following code generates a number with a length of 8, with the number starting with BH and the remaining 6 bits as serial numbers. --get new numbered functions create function f_nextbh () RETURNSChar(8) Asbegin RETURN (SELECT'BH'+right (1000001+isnull (MAX (BH),6),0),6) from TB with (Xlock,paglock)) Endgo--apply the function in the table CREATE table TB (BHChar(8) PRIMARY KEY DEFAULT dbo.f_nextbh (), Colint)--Insert data begin TRAN Insert TB (COL) VALUES (1) INSERT TB (col) VALUES (2) INSERT TB (col) VALUES (3) DELETE TB WHERE Col=3INSERT TB (col) VALUES (4) INSERT TB (bh,col) VALUES (DBO.F_NEXTBH (), -) COMMIT TRAN--Show Results Select*From TB/*--Results bh Col---------------------------BH000001 1bh000002 2bh000003 4bh000004 14--*/CREATE TABLE TB (IDintidentity,name varchar (Ten), Code as 'BH'+right ('0000'+cast (ID asvarchar),5) ) Goinsert TB (name)Select 'A'Union AllSelect 'B'Union AllSelect 'C'Union AllSelect 'D'Select* fromtbdrop Table TB/*ID Name Code---------------------------------1 A BH000012 B BH000023 C BH000034 D BH00004
SQL Server implementation Auto-Grow