An Efficient Method for sequence in sqlserver

An Efficient Method for sequence in sqlserver

If you are familiar with sequence in Oracle and want to implement sequence in sqlserver, you will find that no ready-made sequence object can be created. What should we do?

Of course, this small problem is not difficult for our programmers. "MAX + 1", some people will say this way. Yes, this method is easy to implement. Of course, you may also say that the most SQL method is to use the identity column, and the auto-increment method is OK. However, if you want to implement the format of "yyyymmdd" + Sequence Value (for example, 2008072400001) for such a column, it will not work. You can still use the stored procedure to obtain a sequence value.

 

A common online stored procedure is to create a table for all sequence used in the current database. For example, "allsequence" contains four fields: name, start value, increment value, and current value ], when a sequence is created, a record is created. When the sequence is obtained, the current value of the corresponding row is increased by the incremental value.

 

In systems with less concurrent requests, this process is fine. However, once a concurrent request is in a certain magnitude, this process will often encounter problems.

 

The following is an improvement method suitable for high-throughput access requests, such as thousands of requests per second:

 

-- Assume that you want to create a sequence for t_0101001

-- Create a table seqt_0101001
Create Table seqt_0101001 (

-- The ID column is an auto-increment column.
Seqid int identity (1, 1) primary key,

-- Sequence Value
Seqval varchar (1)
)

-- Create a stored procedure for getting the latest sequence value from the seqt_0101001 table
Create procedure p_getnewseqval_seqt_0101001
As
Begin
-- Declare the new Sequence Value variable
Declare @ newseqvalue int

-- Sets the number of items displayed after the insert or delete operation to cancel.
Set nocount on

-- Insert a new value to the seqt_0101001 table
Insert into seqt_0101001 (seqval) values ('A ')

-- Set the new sequence value to the last Identifier value inserted into the ID column of the seqt_0101001 table.
Set @ newseqvalue = scope_identity ()

-- Delete the seqt_0101001 table (locked rows are not displayed)
Delete from seqt_0101001 with (readpast)

-- Returns the new sequence value.
Return @ newseqvalue

End

-- Use sequence
Declare @ newseqval int
Exec @ newseqval = p_getnewseqval_seqt_0101001
Print @ newseqval

 

To obtain the "20080724000056" format we mentioned just now, we can do the following:

Select convert (char (8), getdate (), 112) + right ('000000' + Cast (@ newseqval as varchar (5), 5) as myseq

 

However, it is still useful. It cannot be directly used in select statements of non-stored procedures.

 

From http://www.cnblogs.com/heekui/archive/2008/07/24/1250842.html

 

Another idea

Create Table sequences (
-- Sequence is a reserved word
SEQ varchar (100) primary key,
Sequence_id int
);

Ms SQL Server Stored Procedure:

Create procedure nextval
@ Sequence varchar (100 ),
@ Sequence_id int output
As

-- Return an error if sequence does not exist
-- So we will know if someone truncates the table
Set @ sequence_id =-1

Update Sequences
Set @ sequence_id = sequence_id + 1
Where seq = @ Sequence

Return @ sequence_id