Implement ORACLE 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 such a column is to be in the format of "YYYYMMDD" + "department number (such as ZX)" + Sequence Value (such as 20100707ZX000002), 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 ()

-- If the Sequence Value of the combination is <999999999 (the value is greater than 201019988), for example, the Sequence Value of "YYYY" + 5 bits () can be implemented in the following way:
-- Set @ NewSeqValue = Convert (char (4), Getdate (), 112) + right ('000000' + CAST (scope_identity () AS varchar (5), 5)
-- Print @ NewSeqVal to get the Sequence value similar to "201019988 ".

-- 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
Print Convert (char (8), Getdate (), 112) + 'zx' + right ('000000' + CAST (@ NewSeqVal AS varchar (5), 6)

 

To obtain the "20100707ZX000002" format we mentioned just now, we can do the following:
Print Convert (char (8), Getdate (), 112) + 'zx' + right ('000000' + CAST (@ NewSeqVal AS varchar (5), 6)

Disadvantage: it cannot be directly used in select statements without stored procedures.

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