Simple SQL, can be used to generate the specified prefix self-increment sequence, such as order number, generate coupon code, etc.

Requirement 1: Order number requirements unique, not too long, self-increment, but not through the morning order number and the night order number subtraction to calculate the platform about a day of the single volume

Requirement 2: Request to generate 10w coupon, require the coupon code unique, not too long, can not easily guess the other coupon code

Based on these requirements, a simple database issuing SQL is provided to meet the above requirements, described below

Increments table design two fields, prefix and maxnum, do not need to design a primary key and a clustered index, to establish a unique index on the prefix, Include[maxnum] field, so that the query can guarantee that the index overlay

The prefix and data splicing services are provided by the application, the database is responsible for getting the increased number, it is recommended to encapsulate the stored procedures, remember that the script itself contains transactions, do not use the application of transaction nesting (in addition, I am not a DBA, the technical level is limited, there is no guarantee that the following SQL will not deadlock)

DECLARE @Prefix nvarchar( +)='C' --input PrefixDECLARE @Qty int=1 --Input Growth StepSET TRANSACTION Isolation  Level READ COMMITTED --Setting the isolation levelBEGIN TRAN --Open Transaction    DECLARE @maxNum bigint=0 --declares the current maximum number of prefixes currently specifiedReTry:--Goto's label    SELECT    @maxNum =Maxnum+ @Qty     fromIncrements with(UPDLOCK)WHEREPrefix= @Prefix --using the update lock query to specify the current seed number + step size of the prefix, if there is a record, only one connection can query to this record, and the other connections are blocked until the transaction commits    IF(@maxNum = 0)--If no previous prefix is found    BEGIN        BEGINTRYSET @maxNum = @maxNum + @Qty            INSERTincrementsVALUES(@Prefix,@maxNum)--inserting a prefix record, setting the current number of seeds equal to the step, the same prefix in concurrent cases may also perform the insert operation, by setting a unique index in prefix to make only one insert successful, the other re-retry        ENDTRYBEGINCATCHGOTOReTry--retry when insert fails        ENDCATCHEND    ELSE    BEGIN        UPDATEincrementsSETMaxnum=Maxnum+ @Qty        WHEREPrefix= @Prefix --If the number of seeds is found, update the record    END    SELECT      @maxNum --returns the result of the current seed count + stepCOMMIT

Order Number: Short date as prefix + random (1-9) step + random number, as

20180108 00016 037 -- example, there are no spaces in the normal case 20180108 00019 233

Coupon code: Batch generated 10w, single letter as a prefix (such as T), step set to 100W, get the number (such as 2000000), generate 1000000-2000000 of the array, through the shuffle algorithm to get 10w elements, each element is converted to 36 binary

Such as

tsm9h   - - corresponding to T1335221TSMC9   - - corresponding T1335321

Then similar to the identity check code, the design of an algorithm to increase the 1-2-bit check code at the beginning or end, such as all the number * itself to add the last one put in the last, example

-- T converted to a number of Tsm9h8   -- corresponding tsm9h 2*2+9*9+1*1+3*3+3*3+5*5+2*2+2*2+1*1=138   TSMC93  -- corresponding TSMC9 2*2+9*9+1*1+3*3+3*3+5*5+3*3+2*2+1*1=143

Simple SQL, can be used to generate the specified prefix self-increment sequence, such as order number, generate coupon code, etc.