Sequence Objects in SQL 2012 and SQL 2008

Sequence Objects are new starting in SQL Server 2012.  They work similarly to an IDENTITY value, but where the IDENTITY value is scoped to a specific column in a specific table, the Sequence Object is scoped to the entire database and controlled by application code.  This can allow you to synchronize seed values across multiple tables that reference one another in a parent child relationship.  Or, with a little bit of code you can also take control on whether or not the next value is used or saved for the next INSERT should the current transaction be rolled back (the IDENTITY value is lost and creates a gap when an INSERT is rolled back).  For applications that require sequence numbering schemes without gaps, Sequence Objects do not solve that entirely.  If a record is deleted or in concurrent inserts with multiple users where one transaction fails – gaps can still appear in your table as the sequence value is not re-used.  To satisfy that requirement, you will still need to create a mechanism (commonly a table of values) yourself and manage them in a way where unused or deleted values are returned to the master table.  However, Sequence Objects give us much greater flexibility in our design with auto-generated number sequences.  To create a Sequence Object in SQL Server 2012, you can simply use the following:

 -- Create a Sequence Object to start at 1 and increment by 1
CREATE SEQUENCE dbo.NextCustomerID
    as BIGINT
    START WITH 1
    INCREMENT BY 1;
GO

-- Get the next available ID
DECLARE @CustID BIGINT;
SELECT @CustID = NEXT VALUE FOR dbo.NextCustomerID;
SELECT @CustID as 'Customer ID';

You can get more information on Sequence Objects here:

Sequence Numbers

https://msdn.microsoft.com/en-us/library/ff878058.aspx

CREATE SEQUENCE (Transact-SQL)

https://msdn.microsoft.com/en-us/library/ff878091.aspx

In SQL Server 2008 R2 and earlier, Sequence objects were not available.  To provide this functionality, we have to build some constructs that allow us to mimic this behavior.

To satisfy the same the requirements as the Sequence Object, a common solution is to build dedicated table with an IDENTITY value where the last IDENTITY value can be returned from within a Stored Procedure or a Function:

 CREATE TABLE dbo.GlobalSequence
(
       id INT IDENTITY(1,1)
)
go

BEGIN TRAN

INSERT INTO dbo.GlobalSequence 
       OUTPUT inserted.id
DEFAULT VALUES

ROLLBACK TRAN

If you must not have gaps in your table in the event of a rolled back transaction or deleted record, you could remove the ROLLBACK TRAN operation (replacing it with a COMMIT) and either delete values once they are committed in your application or use a secondary BIT column to “tombstone” values once they are used. 

Finally, in high throughput/high performance situations it is common to want to pre-allocate a block of numbers ahead of time.  In SQL Server 2012 Sequence Objects, this is handled using the CACHE statement.  For the SQL 2008 R2 and earlier solution above, you can do this in a couple of ways.  One way would be to run a background task (a scheduled task in SQL Agent) that handles allocating a configurable block of IDENTITY values during an off-peak time.  Another solution – or perhaps a backup solution – would be to check the amount of remaining values each time or every nth time a value is requested and allocate more one it reaches a low-water mark.  With the latter approach, most requests will not incur any extra overhead besides the size check but occasionally a user will wait while the table is re-populated.   If pre-allocating values and not deleting them once they are committed, you will likely want to schedule a background process to clean up the custom sequence value table when values are not longer needed.

- Jay