SQL server high concurrency generates unique order number

Preface

A few days ago I wrote an article on how to generate unique order numbers for MySQL high concurrency , someone sent a private message to ask if there is a SQL server version. Today at noon, I wrote a high-concurrency implementation of the SQL server version to generate a unique order number. In fact, the principles of MySQL and SQL server are the same. The main reason is that some of their syntaxes are somewhat different, so you will find that I The text description of this article is almost the same, only the code and interface are different.

Recommended: "SQL Tutorial"

1. Scenario reproduction

In an ERP purchase and sales system or other systems such as 0A, if there are multiple If people perform the operation of generating order numbers at the same time, it is easy for multiple people to obtain the same order number, causing irreparable losses to the company's business

2. How to avoid high concurrency situations where order numbers are not unique

We can use stored procedures and data tables to build a table and create stored procedures. The stored procedure is responsible for generating order numbers, and the table is responsible for handling uniqueness issues.

When the stored procedure generates an order Number, first write the order number into the table, and then display the order number result. When writing the generated order number into the table, two situations will occur. Why? Because our table has a primary key (primary key uniqueness)

• can be written: When there is no same order number in the table, the generated order number is written into the table

• Cannot be written: When the same order number exists in the table, the generated order number cannot be written to the table, and the order number cannot be obtained, thereby ensuring that a unique order number is generated under high concurrency.

3. The process of generating a unique order number under high concurrency conditions

The following will explain the process of generating a unique order number using code and actual operations
Step 1: Create a data table and set the order number field as the primary key (the key to the unique order number)

Step 2: Create a stored procedure to generate the order number
The format of the generated order number is: custom prefix year, month and day suffix (001, 002, 003)
1. First create a stored procedure
The parameter is @BILL_TYPE

CREATE  PROCEDURE [dbo].[GetBillNO]
 @BILL_TYPE nvarchar(3)

2. Generate year, month, day and suffix
Year, month and day are the current system time, and the initial value of the suffix is ​​0

declare @BILL_NO nvarchar(15);declare @currentDate nvarchar(15);declare @lastno INT = 0;set @currentDate = Convert(varchar(10),Getdate(),112);

3. Query form , get the order number of the table
Query the table, get the latest order number with the prefix related to the custom content and assign it to @BILL_NO

select @BILL_NO = isnull(BILL_NO,0)
    FROM temp_bill 
    WHERE SUBSTRING(BILL_NO,1,3) =BILL_TYPE and
    SUBSTRING(BILL_NO,4,8) =@currentDate

4. Generate the order number

• If the order number obtained in the previous step is not empty, the newly generated order number will be 1 on the original order number 1

  Example: Obtained order number: QNB20200615015
  That is, the generated order number is: QNB20200615016

• If the order number obtained in the previous step is empty, the suffix of the newly generated order number is 001

  Example: Generated order number ：QNB20200615001

IF @BILL_NO !='' begin
 SET @lastno = str(right(@BILL_NO, 3)+1) ; 
 set @BILL_NO = @BILL_TYPE+@currentDate+RIGHT('000'+CAST(@lastno as varchar),3);
 end;ELSE  begin
 set @BILL_NO = @BILL_TYPE+@currentDate+CAST('001' as varchar(20));END;

When I did this, since the SQL server did not have the LPAD function (it will automatically complete the number of digits less than the specified number), I used string splicing, and then used right to get the 3 on the right. The target is achieved (¯﹃¯), and it is dead

5. Insert the generated order number into the table
If the same order number exists in the table, the insertion into the table fails
If the same order number does not exist in the table, the insertion into the table is successful

insert into Temp_Bill(BILL_NO,BILL_TYPE) values(@BILL_NO ,@BILL_TYPE )

6. Return the unique order number
When the order is inserted into the table successfully, the unique order number can be returned (if the above If one step is unsuccessful, this step will not be run)

select @BILL_NO as BILL_NO

4. Running results

1. First of all, there is no data in my table, and the prefix will be generated (I entered: UIE) year and month Day (20200615) 001 (the first data, so it is 001)
That is: QNB20200615001

2 When I enter it for the second time, because there is data in the table, it will According to the latest order number, add 1
to the suffix: QNB20200615002

5. Old rules

I hope this article can help everyone
If you have enough points, just download it. If you don’t have points, type on the picture. The amount of code is not big anyway
If you are a student and don’t have points, you can message me privately and I will send you the source code for free
Code Download link: SQL server_getbillno.bak
Code screenshot:

The above is the detailed content of SQL server high concurrency generates unique order number. For more information, please follow other related articles on the PHP Chinese website!