SQL Server generates a unique number

Cardinality table-Used to store number prefixes and types

The following tables are built

CREATE TABLE [dbo]. [Serialno] (
[SCode] [varchar] () not NULL,
[SName] [varchar] (+) NULL,
[SQZ] [varchar] () NULL,
[Svalue] [varchar] () NULL,
CONSTRAINT [Pk_serialno] PRIMARY KEY CLUSTERED
(
[SCode] Asc
) with (Pad_index = off, Statistics_norecompute = off, Ignore_dup_key = off, Allow_row_locks = on, allow_page_locks = ON) O N [PRIMARY]
) on [PRIMARY]

This table needs to be manually added to your prefix

For example:

Stored Procedure Code:

1 Create procedure [dbo].[Getserialno]   2 (   3    @sCode varchar( -)   4 )   5  6    as 7  8 --exec Getserialno9  Ten begin  One   A    Declare @sValue  varchar( -),    -  -            @dToday   datetime,            the  -            @sQZ  varchar( -)--this represents the prefix -   -    Begin Tran      +   -    BeginTry +   A      --Lock the record, a lot of people with lock to lock, starting here just to execute an update on it. at     --in the same thing, the lock is started after the UPDATE statement is executed -      UpdateSerialnoSetSvalue=SvaluewhereSCode=@sCode    -   -      Select @sValue =Svalue fromSerialnowhereSCode=@sCode    -   -      Select @sQZ =SQZ fromSerialnowhereSCode=@sCode    in  -     --there is no record in the factor table, insert initial value--the base data must be manually created to   +     If @sValue  is NULL    -   the      Begin  *   $        Select @sValue = Convert(bigint,Convert(varchar(6),getdate(), A)+ '000001')   Panax Notoginseng   -        UpdateSerialnoSetSvalue=@sValue whereSCode=@sCode    the   +      End A       the    Else    +   -      Begin               --The factor table has records $   $        Select @dToday = substring(@sValue,1,6)    -  -       --if the dates are equal, add 1 the  -        If @dToday = Convert(varchar(6),getdate(), A)   Wuyi   the          Select @sValue = Convert(varchar( -), (Convert(bigint,@sValue)+ 1))    -  Wu       Else              --If the dates are not equal, the date is assigned first, and the serial number starts at 1 -   About         Select @sValue = Convert(bigint,Convert(varchar(6),getdate(), A)+'000001')    $   -            -  -        UpdateSerialnoSetSvalue=@sValue whereSCode=@sCode    A  +      End  the   -      SelectResult= @sQZ+@sValue      $   the      Commit Tran    the   the    EndTry the   -    BeginCatch in   the      Rollback Tran    the   About      SelectResult= 'Error'  the   the    EndCatch the  + End 

Execute: EXEC dbo. Getserialno ' CUS '

Results: CUS150413000001

This method can be used to generate a serial number, use Update to start the database lock, concurrency is not duplicated, readability is better, of course, the use of GUIDs is another matter.

SQL Server generates a unique number