How would you implement sequences in Microsoft SQL Server?

Question

Does anyone have a good way of implementing something like a sequence in SQL server?

Sometimes you just don't want to use a GUID, besides the fact that they are ugly as heck. Maybe the sequence you want isn't numeric? Besides, inserting a row and then asking the DB what the number is just seems so hackish.

Solution 1

Sql Server 2012 has introduced SEQUENCE objects, which allow you to generate sequential numeric values not associated with any table.

Creating them are easy:

CREATE SEQUENCE Schema.SequenceName
AS int
INCREMENT BY 1 ;

An example of using them before insertion:

DECLARE @NextID int ;
SET @NextID = NEXT VALUE FOR Schema.SequenceName;
-- Some work happens
INSERT Schema.Orders (OrderID, Name, Qty)
  VALUES (@NextID, 'Rim', 2) ;

See my blog for an in-depth look at how to use sequences:

http://sqljunkieshare.com/2011/12/11/sequences-in-sql-server-2012-implementingmanaging-performance/

Solution 2

As sqljunkieshare correctly said, starting from SQL Server 2012 there is a built-in SEQUENCE feature.

The original question doesn't clarify, but I assume that requirements for the Sequence are:

1. It has to provide a set of unique growing numbers
2. If several users request next value of the sequence simultaneously they all should get different values. In other words, uniqueness of generated values is guaranteed no matter what.
3. Because of possibility that some transactions can be rolled back it is possible that end result of generated numbers will have gaps.

I'd like to comment the statement in the original question:

"Besides, inserting a row and then asking the DB what the number just seems so hackish."

Well, there is not much we can do about it here. The DB is a provider of the sequential numbers and DB handles all these concurrency issues that you can't handle yourself. I don't see alternative to asking the DB for the next value of the sequence. There has to be an atomic operation "give me the next value of the sequence" and only DB can provide such atomic operation. No client code can guarantee that he is the only one working with the sequence.

To answer the question in the title "how would you implement sequences" - We are using 2008, which doesn't have the SEQUENCE feature, so after some reading on this topic I ended up with the following.

For each sequence that I need I create a separate helper table with just one IDENTITY column (in the same fashion as in 2012 you would create a separate Sequence object).

CREATE TABLE [dbo].[SequenceContractNumber]
(
    [ContractNumber] [int] IDENTITY(1,1) NOT NULL,

    CONSTRAINT [PK_SequenceContractNumber] PRIMARY KEY CLUSTERED ([ContractNumber] ASC)
)

You can specify starting value and increment for it. Then I create a stored procedure that would return the next value of the sequence. Procedure would start a transaction, insert a row into the helper table, remember the generated identity value and roll back the transaction. Thus the helper table always remains empty.

CREATE PROCEDURE [dbo].[GetNewContractNumber]
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    DECLARE @Result int = 0;

    IF @@TRANCOUNT > 0
    BEGIN
        -- Procedure is called when there is an active transaction.
        -- Create a named savepoint
        -- to be able to roll back only the work done in the procedure.
        SAVE TRANSACTION ProcedureGetNewContractNumber;
    END ELSE BEGIN
        -- Procedure must start its own transaction.
        BEGIN TRANSACTION ProcedureGetNewContractNumber;
    END;

    INSERT INTO dbo.SequenceContractNumber DEFAULT VALUES;

    SET @Result = SCOPE_IDENTITY();

    -- Rollback to a named savepoint or named transaction
    ROLLBACK TRANSACTION ProcedureGetNewContractNumber;

    RETURN @Result;
END

Few notes about the procedure.

First, it was not obvious how to insert a row into a table that has only one identity column. The answer is DEFAULT VALUES.

Then, I wanted procedure to work correctly if it was called inside another transaction. The simple ROLLBACK rolls back everything if there are nested transactions. In my case I need to roll back only INSERT into the helper table, so I used SAVE TRANSACTION.

ROLLBACK TRANSACTION without a savepoint_name or transaction_name rolls back to the beginning of the transaction. When nesting transactions, this same statement rolls back all inner transactions to the outermost BEGIN TRANSACTION statement.

This is how I use the procedure (inside some other big procedure that, for example, creates a new contract):

DECLARE @VarContractNumber int;
EXEC @VarContractNumber = dbo.GetNewContractNumber;

It all works fine if you need to generate sequence values one at a time. In case of contracts, each contract is created individually, so this approach works perfectly. I can be sure that all contracts always have unique contract numbers.

NB: Just to prevent possible questions. These contract numbers are in addition to surrogate identity key that my Contracts table has. The surrogate key is internal key that is used for referential integrity. The generated contract number is a human-friendly number that is printed on the contract. Besides, the same Contracts table contains both final contracts and Proposals, which can become contracts or can remain as proposals forever. Both Proposals and Contracts hold very similar data, that's why they are kept in the same table. Proposal can become a contract by simply changing the flag in one row. Proposals are numbered using a separate sequence of numbers, for which I have a second table SequenceProposalNumber and second procedure GetNewProposalNumber.

---

Recently, though, I came across a problem. I needed to generate sequence values in a batch, rather than one-by-one.

I need a procedure that would process all payments that were received during a given quarter in one go. The result of such processing could be ~20,000 transactions that I want to record in the Transactions table. I have similar design here. Transactions table has internal IDENTITY column that end user never sees and it has a human-friendly Transaction Number that would be printed on the statement. So, I need a way to generate a given number of unique values in a batch.

Essentially, I used the same approach, but there are few peculiarities.

First, there is no direct way to insert multiple rows in a table with only one IDENTITY column. Though there is a workaround by (ab)using MERGE, I didn't use it in the end. I decided that it was easier to add a dummy Filler column. My Sequence table is going to be always empty, so extra column doesn't really matter.

The helper table looks like this:

CREATE TABLE [dbo].[SequenceS2TransactionNumber]
(
    [S2TransactionNumber] [int] IDENTITY(1,1) NOT NULL,
    [Filler] [int] NULL,
    CONSTRAINT [PK_SequenceS2TransactionNumber] 
    PRIMARY KEY CLUSTERED ([S2TransactionNumber] ASC)
)

The procedure looks like this:

-- Description: Returns a list of new unique S2 Transaction numbers of the given size
-- The caller should create a temp table #NewS2TransactionNumbers,
-- which would hold the result
CREATE PROCEDURE [dbo].[GetNewS2TransactionNumbers]
    @ParamCount int -- not NULL
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    IF @@TRANCOUNT > 0
    BEGIN
        -- Procedure is called when there is an active transaction.
        -- Create a named savepoint
        -- to be able to roll back only the work done in the procedure.
        SAVE TRANSACTION ProcedureGetNewS2TransactionNos;
    END ELSE BEGIN
        -- Procedure must start its own transaction.
        BEGIN TRANSACTION ProcedureGetNewS2TransactionNos;
    END;

    DECLARE @VarNumberCount int;
    SET @VarNumberCount = 
    (
        SELECT TOP(1) dbo.Numbers.Number
        FROM dbo.Numbers
        ORDER BY dbo.Numbers.Number DESC
    );

    -- table variable is not affected by the ROLLBACK, so use it for temporary storage
    DECLARE @TableTransactionNumbers table
    (
        ID int NOT NULL
    );

    IF @VarNumberCount >= @ParamCount
    BEGIN
        -- the Numbers table is large enough to provide the given number of rows
        INSERT INTO dbo.SequenceS2TransactionNumber
        (Filler)
        OUTPUT inserted.S2TransactionNumber AS ID INTO @TableTransactionNumbers(ID)
        -- save generated unique numbers into a table variable first
        SELECT TOP(@ParamCount) dbo.Numbers.Number
        FROM dbo.Numbers
        OPTION (MAXDOP 1);

    END ELSE BEGIN
        -- the Numbers table is not large enough to provide the given number of rows
        -- expand the Numbers table by cross joining it with itself
        INSERT INTO dbo.SequenceS2TransactionNumber
        (Filler)
        OUTPUT inserted.S2TransactionNumber AS ID INTO @TableTransactionNumbers(ID)
        -- save generated unique numbers into a table variable first
        SELECT TOP(@ParamCount) n1.Number
        FROM dbo.Numbers AS n1 CROSS JOIN dbo.Numbers AS n2
        OPTION (MAXDOP 1);

    END;

    /*
    -- this method can be used if the SequenceS2TransactionNumber
    -- had only one identity column
    MERGE INTO dbo.SequenceS2TransactionNumber
    USING
    (
        SELECT *
        FROM dbo.Numbers
        WHERE dbo.Numbers.Number <= @ParamCount
    ) AS T
    ON 1 = 0
    WHEN NOT MATCHED THEN
    INSERT DEFAULT VALUES
    OUTPUT inserted.S2TransactionNumber
    -- return generated unique numbers directly to the caller
    ;
    */

    -- Rollback to a named savepoint or named transaction
    ROLLBACK TRANSACTION ProcedureGetNewS2TransactionNos;

    IF object_id('tempdb..#NewS2TransactionNumbers') IS NOT NULL
    BEGIN
        INSERT INTO #NewS2TransactionNumbers (ID)
        SELECT TT.ID FROM @TableTransactionNumbers AS TT;
    END

END

And this is how it is used (inside some big stored procedure that calculates transactions):

-- Generate a batch of new unique transaction numbers
-- and store them in #NewS2TransactionNumbers
DECLARE @VarTransactionCount int;
SET @VarTransactionCount = ...

CREATE TABLE #NewS2TransactionNumbers(ID int NOT NULL);

EXEC dbo.GetNewS2TransactionNumbers @ParamCount = @VarTransactionCount;

-- use the generated numbers...
SELECT ID FROM #NewS2TransactionNumbers AS TT;

There are few things here that require explanation.

I need to insert a given number of rows into the SequenceS2TransactionNumber table. I use a helper Numbers table for this. This table simply holds integer numbers from 1 to 100,000. It is used in other places in the system as well. I check if there is enough rows in the Numbers table and expand it to 100,000 * 100,000 by cross joining with itself if needed.

I have to save the result of the bulk insert somewhere and pass it to the caller somehow. One way to pass a table outside of the stored procedure is to use a temporary table. I can't use table-valued parameter here, because it is read-only unfortunately. Also, I can't directly insert the generated sequence values into the temporary table #NewS2TransactionNumbers. I can't use #NewS2TransactionNumbers in the OUTPUT clause, because ROLLBACK will clean it up. Fortunately, the table variables are not affected by the ROLLBACK.

So, I use table variable @TableTransactionNumbers as a destination of the OUTPUT clause. Then I ROLLBACK the transaction to clean up the Sequence table. Then copy the generated sequence values from table variable @TableTransactionNumbers to the temporary table #NewS2TransactionNumbers, because only temporary table #NewS2TransactionNumbers can be visible to the caller of the stored procedure. The table variable @TableTransactionNumbers is not visible to the caller of the stored procedure.

Also, it is possible to use OUTPUT clause to send the generated sequence directly to the caller (as you can see in the commented variant that uses MERGE). It works fine by itself, but I needed the generated values in some table for further processing in the calling stored procedure. When I tried something like this:

INSERT INTO @TableTransactions (ID)
EXEC dbo.GetNewS2TransactionNumbers @ParamCount = @VarTransactionCount;

I was getting an error

Cannot use the ROLLBACK statement within an INSERT-EXEC statement.

But, I need ROLLBACK inside the EXEC, that's why I ended up having so many temporary tables.

After all this, how nice would it be to switch to the latest version of SQL server which has a proper SEQUENCE object.

Solution 3

You could just use plain old tables and use them as sequences. That means your inserts would always be:

BEGIN TRANSACTION  
SELECT number from plain old table..  
UPDATE plain old table, set the number to be the next number  
INSERT your row  
COMMIT  

But don't do this. The locking would be bad...

I started on SQL Server and to me, the Oracle "sequence" scheme looked like a hack. I guess you are coming from the other direction and to you, and scope_identity() looks like a hack.

Get over it. When in Rome, do as the Romans do.

Solution 4

An Identity column is roughly analogous to a sequence.

Solution 5

The way that i used to solve this problem was a table 'Sequences' that stores all my sequences and a 'nextval' stored procedure.

Sql Table:

CREATE TABLE Sequences (  
    name VARCHAR(30) NOT NULL,  
    value BIGINT DEFAULT 0 NOT NULL,  
    CONSTRAINT PK_Sequences PRIMARY KEY (name)  
);

The PK_Sequences is used just to be sure that there will never be sequences with the same name.

Sql Stored Procedure:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'nextVal') AND type in (N'P', N'PC')) DROP PROCEDURE nextVal;  
GO  
CREATE PROCEDURE nextval  
    @name VARCHAR(30)  
AS  
    BEGIN  
        DECLARE @value BIGINT  
        BEGIN TRANSACTION  
            UPDATE Sequences  
            SET @value=value=value + 1  
            WHERE name = @name;  
            -- SELECT @value=value FROM Sequences WHERE name=@name  
        COMMIT TRANSACTION  
        SELECT @value AS nextval  
    END;  

Insert some sequences:

INSERT INTO Sequences(name, value) VALUES ('SEQ_Workshop', 0);
INSERT INTO Sequences(name, value) VALUES ('SEQ_Participant', 0);
INSERT INTO Sequences(name, value) VALUES ('SEQ_Invoice', 0);  

Finally get next value of a sequence,

execute nextval 'SEQ_Participant';

Some c# code to get the next value from Sequence table,

public long getNextVal()
{
    long nextval = -1;
    SqlConnection connection = new SqlConnection("your connection string");
    try
    {
        //Connect and execute the select sql command.
        connection.Open();

        SqlCommand command = new SqlCommand("nextval", connection);
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.Add("@name", SqlDbType.NVarChar).Value = "SEQ_Participant";
        nextval = Int64.Parse(command.ExecuteScalar().ToString());

        command.Dispose();
    }
    catch (Exception) { }
    finally
    {
        connection.Dispose();
    }
    return nextval;
}

View more solutions

Comments

[Matt Calhoun]

Roughly yes, but not quite. You need to insert something into the table before you can be sure of the ID.

[MacAnthony]

What seems hackish in sequences? Just curious, I learned to use sequences first. Like you mention, whatever we learn first tends to feel "right".

[rapidfyre]

In SQL Server, I just specify the column as IDENTITY and I'm done. In Oracle, I needed to do more (Sorry, I forgot the details. It was 8 years ago...).

[MacAnthony]

Ah, I see what your saying. For Oracle that is correct, and it is a pain. In Postgres, you can have a columns default value be the sequence's nextval, and it is very handy.

[matt b]

perhaps I should have said "very, very roughly" :)

[MusiGenesis]

I hear you. Everything has upsides and downsides. I'm actually a big natural key fan, so I usually only use GUIDs if no natural key is available. So a customer table would probably be PKed by SSN or a compound PK of name and address etc.

[Jonas Lincoln]

And you can also get gaps in your sequence, if you rollback an insert.

[Clavijo]

Is this atomic? If two callers called nextval at the exact same time, would they get the the same number?

[Georgios Syngouroglou]

This solution is not solving the concurrently request for a new id problem. If two or more clients call the nextval procedure at the same absolutely time then it is not clear what will be append. It is a solution for a small system. This solution solves only my pre known id problem, when insertind data.

[Vladimir Baranov]

@eidylon, in previous versions of SQL Server it is possible to emulate the sequence object using dedicate table with identity column, as I explained in my answer. Though, in complex cases when you need to generate more than one value it gets pretty ugly.

[Shaiju T]

Here is another similar article, hope helps some one.

[Shaiju T]

Did you create clustered primary key for faster query and what is ASC and PK_SequenceContractNumber in CONSTRAINT [PK_SequenceContractNumber] PRIMARY KEY CLUSTERED ([ContractNumber] ASC) ?

[Vladimir Baranov]

@stom, the SequenceContractNumber table has a clustered primary key. PK_SequenceContractNumber is the name of the primary key constraint and corresponding index that is created to implement the constraint. ASC means ascending sort order of ContractNumber in the index. The main point of the method that I described here is that the helper table with IDENTITY column always remain empty. A row is inserted, the generated ID is recorded and transaction is rolled back.

[SQL Police]

This solution is not optimal. `nextval´should at least set an exclusive lock on the table. Otherwise two concurrent calls will return the same number.

[mike]

@Clavijo -- Definitely not Atomic, yes two users could get the same value using the above code. I just posted an answer to this question that (i believe) does allow for Atomic sequences and handles concurrency issues.

[mike]

This is the right outline, but does not handle Concurrency issues. I just posted some code in an answer that is transaction-safe and handles multiple current transactions.

[Matt Gibson]

SQL Server SEQUENCE objects are already transaction safe, though. If you pull a number from a sequence inside a transaction and someone else pulls one before your transaction is complete, they just get the next number along. Just like with Oracle, I believe. You may want to add your reasons for using this method rather than the built-in functionality...

[Bacon Bits]

@JonasLincoln You can get gaps in a SEQUENCE, too. There is essentially no way to ensure you will never get gaps.

[Bacon Bits]

An identity column works very well, especially combined with an OUTPUT clause to return the created ID value.

[mike]

@MattGibson - YES! Correct. This code would only be useful to people using SQLServer version Prior to 2012.