SQL 2012+

Using Sequences in SQL Server

by Richard Carr, published at http://www.blackwasp.co.uk/SQLSequence.aspx

SQL Server 2012 introduced the concept of sequences. These allow the automatic generation of series of numbers. They are similar to identity columns but do not need to be linked to a column.

What is a Sequence?

A sequence in SQL Server 2012 and later editions automatically generates an evenly-spaced series of integer values. Often, the first number from a sequence is one, and each subsequent request returns a value that is one higher than the previous. However, you can set the starting value and the increment between numbers.

Much of the functionality of a sequence is similar to that of an identity column. There are two key differences. Firstly, an identity is only used to generate values for a column. A sequence is not tied to a table, so can be used from stored procedures, triggers, default values and other items. Secondly, a sequence can be given maximum and minimum values and be set to cycle, potentially repeating values.

This functionality makes sequences very useful where an identity column is not ideal. For example, you may wish to have two sets of sequential values in a single table, where only one identity column is permitted. You might also want to share a sequence of values over multiple tables, ensuring that the combined set of values from those tables is unique.

Creating and Using a Simple Sequence

You can create a basic sequence using the CREATE SEQUENCE statement with a unique name for the new item. No other details are required. To demonstrate, in a test database, run the following statement:

CREATE SEQUENCE SimpleSequence

To obtain a value from the sequence, use the NEXT VALUE FOR command with the sequence name. Let's try it using a basic SELECT statement. Execute the following command:

SELECT NEXT VALUE FOR SimpleSequence  -- -9223372036854775808

The result might be a surprise. Instead of the default value of one provided by an identity column, a sequence's standard starting point is the lowest possible value of its data type. Unless specified otherwise, the data type for a sequence is BIGINT, so the minimum value is -9,223,372,036,854,775,808.

Run the statement several times to see the results. Each value returned will be one greater than the previous, as the default increment for a sequence is 1. Once you have seen the results, you can delete the sequence permanently with the DROP SEQUENCE command, as follows:

DROP SEQUENCE SimpleSequence

Setting the Initial Value and Increment

If the value from a sequence will be seen by users, you will probably want to change the starting number. You can do this by adding the START WITH clause and a replacement value.

The following creates a sequence with an initial value of one:

CREATE SEQUENCE SequenceWithStart
START WITH 1

SELECT NEXT VALUE FOR SequenceWithStart  -- 1

To change the increment used between values in the sequence, add an INCREMENT BY clause. The number may be negative if the sequence should decrease in value for each item:

CREATE SEQUENCE SequenceTenFive
START WITH 10
INCREMENT BY 5

SELECT NEXT VALUE FOR SequenceTenFive  -- 10
SELECT NEXT VALUE FOR SequenceTenFive  -- 5

Setting the Range

In addition to setting the starting value and the increment, you can specify a valid range for a sequence using the MINVALUE and MAXVALUE clauses. In a normal sequence, you can continue to retrieve values until the obtained number is outside of the range, at which time an error will be raised.

Try running the following statements to create and use a sequence with a small range. Note that the last successfully retrieved value is the inclusive limit of the range. The sixth attempt to read the sequence causes an error.

CREATE SEQUENCE SequenceOneToFive
START WITH 1
MINVALUE 1
MAXVALUE 5 

SELECT NEXT VALUE FOR SequenceOneToFive  -- 1
SELECT NEXT VALUE FOR SequenceOneToFive  -- 2
SELECT NEXT VALUE FOR SequenceOneToFive  -- 3
SELECT NEXT VALUE FOR SequenceOneToFive  -- 4
SELECT NEXT VALUE FOR SequenceOneToFive  -- 5
SELECT NEXT VALUE FOR SequenceOneToFive  -- Error

Creating a Cycling Sequence

Sequences can be set to cycle through the values and then restart, rather than raise an error. For ascending sequences, the value restarts with the minimum once the range is exhausted. This might not be the same as the starting value.

To make a sequence restart, add the CYCLE clause. The following example cycles through the values between one and five, starting with three:

CREATE SEQUENCE SequenceOneToFiveCycle
START WITH 3
MINVALUE 1
MAXVALUE 5
CYCLE

SELECT NEXT VALUE FOR SequenceOneToFiveCycle  -- 3
SELECT NEXT VALUE FOR SequenceOneToFiveCycle  -- 4
SELECT NEXT VALUE FOR SequenceOneToFiveCycle  -- 5
SELECT NEXT VALUE FOR SequenceOneToFiveCycle  -- 1
SELECT NEXT VALUE FOR SequenceOneToFiveCycle  -- 2
SELECT NEXT VALUE FOR SequenceOneToFiveCycle  -- 3