SQL Server generates a unique number

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 Getserialno 9 begin each De Clare @sValue varchar (+), @dToday datetime, @sQZ varchar (50)-this represents a prefix Tran begin Try 21 22-Lock the record, a lot of people use lock to lock, starting here as long as the execution of an update can be 23-in the same thing, executed u After the pdate statement, the lock is started Serialno set svalue=svalue where [email protected] @sValue = Svalu   e from Serialno where [email protected], Select @sQZ = SQZ from Serialno where [email protected]  29 30--there is no record in the factor table, insert initial value--the base data must be manually created by the If @sValue is null for the beginning of the @sValue =  Convert (bigint, CONVERT (varchar (6), GETDATE (), +) + ' 000001 ') Notoginseng Update serialno set [email protected]   where [email protected] (end41)--the factor table has records 45 46     Select @dToday = substring (@sValue, 1,6) 47 48--If the date is equal, add 1 if @dToday = CONVERT (varchar (6), GE              Tdate (), 1 (), Select @sValue = convert (varchar), (CONVERT (bigint, @sValue) +) --If the date is not equal, then the date is assigned, and the serial number starts at 1, and the series is a Select @sValue = convert (bigint, CONVERT (varchar (6), GETDATE (), 12) +   ' 000001 ') Update serialno set svalue [email protected] where [email protected]  ---------------[result = @[email protected]  Rollback Tran--------the "Error"

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