Function with the latest number

Source:

1.Several machines simultaneously append records to the database. Avoid repeated fields?

2. obtain the latest number: The field is of the character type.

Obtain the maximum number of a field in SQL.

 

-- Get the function with the latest number
Create Function f_newid ()
Returns char (7) -- fixed number of digits. The Retrieval Efficiency of char is higher than that of varchar.
As
Begin
Declare @ Re char (7)
Select @ Re = max (BHID) from table
Return (
Case when @ Re is null then 'bh0000001'
Else 'bh '+ right ('000000' + Cast (right (@ Re, 5) as INT) + 1 as varchar), 5)

End)
End
Go

-- Test table. The table name corresponds to the table name in the function.
Create Table (
BHID char (7) default DBO. f_newid () -- sets the default value and automatically generates the number.
Primary Key, -- set it to a primary key to prevent number conflict
-- Constraint unique_bhid _ table unique. -- if a unique constraint is used, delete the preceding primary key constraint statement. Use this statement instead.
Txt1 varchar (10 ),
Txt2 varchar (10 ),
Num float)
Go

/* -- When inserting data, you can ignore the number field and directly use this statement.
When multiple users insert data at the same time, if the number is repeated, an error occurs.
At this time, the front-endProgramInterception error. If it is a violation of the constraints
You only need to re-execute the insert statement. At this time, the number is automatically re-generated.
The re-execution of the insert statement is also very convenient, because the statement does not need to be modified.
--*/
Insert table (txt1, txt2, num) values ('A', 'bb ', 1)

Go
-- Delete test
Drop table
Drop function f_newid