SQL Server implements Oracle's sequence method _mssql

Of course, this small problem is difficult to fail US programmers, "max+1 ah", some people would say such a way. Yes, this approach is easier to achieve. Of course, you may also say that the most SQL way is to use the identity column, the increase in the way to add on the OK. However, such a column would not be in a format that would implement the "YYYYMMDD" +sequence value (for example: 2008072400001). Or honest with the stored procedures to get a sequence value it, the use of the arbitrary.

A common online stored procedure is to create a table for all the current libraries using sequence, such as "Allsequence", which contains four fields "name, starting point value, recursive value, current value", create a record when creating sequence. When the sequence is obtained, it is incremented from the current value of the corresponding row to the increment value.

In systems with less concurrent requests, there is no problem with this process. However, once a concurrency request is at a certain magnitude, the process often encounters problems.

The following is an improved approach that is appropriate for high throughput access requests, such as thousands of requests per second:

Copy Code code as follows:

--Suppose you want to create a sequence for t_0101001

--Create TABLE seqt_0101001
CREATE TABLE seqt_0101001 (

--ID column self-added
seqid int Identity (1,1) primary key,

--Sequence value
Seqval varchar (1)
)

--Create a stored procedure that obtains the latest sequence value from the seqt_0101001 table
CREATE PROCEDURE p_getnewseqval_seqt_0101001
As
Begin
--Declaring a new sequence value variable
DECLARE @NewSeqValue int

--Set the number of bars after inserting and deleting to display cancel
Set NOCOUNT on

--Inserts a new value into the seqt_0101001 table
Insert into seqt_0101001 (seqval) VALUES (' a ')

--Sets the new sequence value to the last identifying value in the identity column inserted into the seqt_0101001 table
Set @NewSeqValue = Scope_identity ()

--Deletes the seqt_0101001 table (does not show the locked line)
Delete from seqt_0101001 with (READPAST)

--Returns the new sequence value
Return @NewSeqValue

End

--Using sequence
Declare @NewSeqVal int
Exec @NewSeqVal = p_getnewseqval_seqt_0101001
Print @NewSeqVal

To get the "20080724000056" format that we mentioned just now, here's what you can do.

Copy Code code as follows:

Select Convert (char (8), Getdate (), 112) + right (' 00000 ' +cast (@NewSeqVal as varchar (5)), 5) as Myseq

But it's still a useful, unpleasant spot. It cannot be directly used directly in statements that are not stored in a select procedure.