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.

Answer 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/

Answer 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.