SQL Server Sequence Numbers

By: Ray Barley | Comments (2) | Related: 1 | 2 | 3 | 4 | 5 | > Identities

Problem

Sequence Numbers are a feature released in SQL Server 2012 and later. Can you give me the details on this feature?

Solution

Essentially a sequence number can be used in lieu of an IDENTITY column, but it also has some other interesting capabilities. Books on Line describes a sequence number as "a user-defined schema-bound object that generates a sequence of numeric values according to the specification with which the sequence was created".

In this tip I'll go through the details on sequence numbers, do a comparison with IDENTITY columns, and provide some scenarios where it could be useful.

Create Sequence

Let's start out by reviewing the CREATE SEQUENCE Transact-SQL statement.

CREATE SEQUENCE [schema_name . ] sequence_name
        [ <sequence_property_assignment> [ ,...n ] ]
    [ ; ]
<sequence_property_assignment>::=
{
    [ AS { built_in_integer_type | user-defined_integer_type } ]
    | START WITH <constant> 
        | INCREMENT BY <constant> 
        | { MINVALUE <constant> | NO MINVALUE }
        | { MAXVALUE <constant> | NO MAXVALUE }
        | { CYCLE | NO CYCLE }
        | { CACHE [<constant> ] | NO CACHE }
}

The following are the main points about the CREATE SEQUENCE statement:

Specify the schema where you want to create the sequence
Specify the name of the sequence
The sequence type can be any of the built-in integer types; e.g. tinyint, smallint, int, bigint, decimal or numeric; decimal or numeric require a scale of 0
The sequence type can also be a user-defined type that is based on one of the built-in integer types
The default sequence type is INT
Use START WITH <integer constant> to specify the first sequence number to be assigned; this can be a negative number
INCREMENT BY <integer constant> determines the next value assigned by the sequence number; this can be a positive or negative value but not 0
MINVALUE <integer constant> and MAXVALUE <integer constant> provide the bounds for the sequence number; the default for MINVALUE is 0 for a tinyint and the smallest negative number for the type of the sequence number; the default for MAXVALUE is the maximum value for the type of the sequence number
Specify CYCLE to restart the sequence number at the MINVALUE after the MAXVALUE is reached
Specify NO CYCLE to throw an exception after the MAXVALUE is reached rather than restarting with the MINVALUE
Use CACHE <integer constant> to retrieve a sequential block of sequence numbers

Use the NEXT VALUE FOR statement to assign and retrieve the next value for a sequence number.

Sequence Number Examples

The following examples will show the basic use cases for a sequence number. The examples use the following schema and table:

CREATE SCHEMA mssqltips
GO
CREATE TABLE mssqltips.Tip (
  TipNumber INT PRIMARY KEY
, Title VARCHAR(50)
)
GO

Create a sequence number in the mssqltips schema to assign tip numbers on the MSSQLTips web site:

CREATE SEQUENCE mssqltips.TipNumber
AS INT
START WITH 1
INCREMENT BY 1
MINVALUE 1
GO

Get the next tip number and assign to a T-SQL variable (you could use @TipNumber in the VALUES clause of one or more INSERT statements):

DECLARE @TipNumber As INT = NEXT VALUE FOR mssqltips.TipNumber
PRINT @TipNumber

Insert a row into a table and get the next value for the TipNumber:

INSERT INTO mssqltips.Tip (TipNumber, Title)
VALUES (NEXT VALUE FOR mssqltips.TipNumber, 'What''s New in Denali? - Sequence Numbers')

Sequence Number Versus Identity Column

A column in a table can have the IDENTITY property which provides for automatically assigning the next value to the column on insert. Now that we have an idea of how to create and use a sequence number, let's compare and contrast the sequence number with the identity column.

An identity column is tied to a table; you have to insert a row in order to get the next value. You can use the NEXT VALUE FOR statement to assign and retrieve the next value for a sequence number; it is not tied to a particular column in a table and you do not have to insert a row to get the next value.
A sequence number can be used to provide an automatically generated number that is used over multiple tables; the identity column is tied to a single table.
You can specify the starting value and increment with an identity column; with a sequence number you can specify the starting value, increment, minimum, maximum, caching, and whether to recycle the values
The NEXT VALUE FOR statement includes an OVER clause allowing you to assign sequence numbers based on an ORDER BY; an identity column does not have this capability.

Which One Should I Choose?

Choose a sequence number in these scenarios:

You want the next value without having to insert a row into a table.
You want to use the value across multiple tables.
You want to automatically recycle the values.

Choose an IDENTITY in these scenarios:

You want to assign the next value on insert for a single table.
You don't need to know the value before you perform the insert.

Miscellaneous Notes

To retrieve a range of values from a sequence number, use the sys.sp_sequence_get_range stored procedure.
To retrieve information about sequence numbers, use the sys.sequences view.
Other related Transact-SQL commands are ALTER SEQUENCE and DROP SEQUENCE.
The NEXT VALUE FOR statement is not run as part of the current transaction; a rollback does not affect the sequence number(s) already assigned.

Next Steps

Sequence Numbers are a potentially useful alternative to using the identities.
Get all the details on SEQUENCE NUMBERS from Creating and Using Sequence Numbers and CREATE SEQUENCE in the product documentation.
For more information on identity columns, read these tips.

About the author

Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips