SQL Server Sequence

Summary: in this tutorial, you will learn about the SQL Server Sequence objects to generate a sequence of numeric values based on a specified specification.

What is a sequence

A sequence is simply a list of numbers, in which their orders are important. For example, the {1,2,3} is a sequence while the {3,2,1} is an entirely different sequence.

In SQL Server, a sequence is a user-defined schema-bound object that generates a sequence of numbers according to a specified specification. A sequence of numeric values can be in ascending or descending order at a defined interval and may cycle if requested.

SQL Server CREATE SEQUENCE statement

To create a new sequence object, you use the CREATE SEQUENCE statement as follows:

CREATE SEQUENCE [schema_name.] sequence_name  
    [ AS integer_type ]  
    [ START WITH start_value ]  
    [ INCREMENT BY increment_value ]  
    [ { MINVALUE [ min_value ] } | { NO MINVALUE } ]  
    [ { MAXVALUE [ max_value ] } | { NO MAXVALUE } ]  
    [ CYCLE | { NO CYCLE } ]  
    [ { CACHE [ cache_size ] } | { NO CACHE } ];
Code language: SQL (Structured Query Language) (sql)

Let’s examine the syntax in detail:

sequence_name

Specify a name for the sequence which is uniquely in the current database.

AS integer_type

Use any valid integer type for the sequence e.g., TINYINT, SMALLINT, INT, BIGINT, or DECIMAL and NUMERIC with a scale of 0. By default, the sequence object uses BIGINT.

START WITH start_value

Specify the first value that the sequence returns. The start_value must be between the range (min_value, max_value).

The start_value defaults to the min_value in an ascending sequence and max_value in a descending sequence.

INCREMENT BY increment_value

Specify the increment_value of the sequence object when you call the NEXT VALUE FOR function.

If increment_value is negative, the sequence object is descending; otherwise, the sequence object is ascending. Note that the increment_value cannot be zero.

[ MINVALUE min_value | NO MINVALUE ]

Specify the lower bound for the sequence object. It defaults to the minimum value of the data type of the sequence object i.e., zero for TINYINT and a negative number for all other data types.

[ MAXVALUE max_value | NO MAXVALUE]

Specify the upper bound for the sequence object. It defaults to the maximum value of the data type of the sequence object.

[ CYCLE | NO CYCLE ]

Use CYCLE if you want the value of the sequence object to restart from the min_value for the ascending sequence object, or max_value for the descending sequence object or throw an exception when its min_value or max_value is exceeded. SQL Server uses NO CYCLE by default for new sequence objects.

[ CACHE cache_size ] | NO CACHE ]

Specify the number of values to cache to improve the performance of the sequence by minimizing the number of disk I/O required to generate sequence numbers. By default, SQL Server uses NO CACHE for new sequence objects.

SQL Server Sequence examples

Let’s take some examples of creating sequences.

A) Creating a simple sequence example

The following statement uses the CREATE SEQUENCE statement to create a new sequence named item_counter with the type of integer (INT), which starts from 10 and increments by 10:

CREATE SEQUENCE item_counter
    AS INT
    START WITH 10
    INCREMENT BY 10;
Code language: SQL (Structured Query Language) (sql)

You can view the sequence object under in the Programmability > Sequences as shown in the following picture:

The following statement returns the current value of the item_counter sequence:

SELECT NEXT VALUE FOR item_counter;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

Current_value
-------------
10

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

In this example, the NEXT VALUE FOR function generates a sequence number from the item_counter sequence object.

Each time you execute the following statement again, you will see that the value of the item_counter will be incremented by 10:

SELECT NEXT VALUE FOR item_counter;
Code language: SQL (Structured Query Language) (sql)

This time the output is:

Current_value
-------------
20

(1 row affected)    
Code language: SQL (Structured Query Language) (sql)

B) Using a sequence object in a single table example

First, create a new schema named procurement:

CREATE SCHEMA procurement;
GO
Code language: SQL (Structured Query Language) (sql)

Next, create a new table named orders:

CREATE TABLE procurement.purchase_orders(
    order_id INT PRIMARY KEY,
    vendor_id int NOT NULL,
    order_date date NOT NULL
);
Code language: SQL (Structured Query Language) (sql)

Then, create a new sequence object named order_number that starts with 1 and is incremented by 1:

CREATE SEQUENCE procurement.order_number 
AS INT
START WITH 1
INCREMENT BY 1;
Code language: SQL (Structured Query Language) (sql)

After that, insert three rows into the procurement.purchase_orders table and uses values generated by the procurement.order_number sequence:

INSERT INTO procurement.purchase_orders
    (order_id,
    vendor_id,
    order_date)
VALUES
    (NEXT VALUE FOR procurement.order_number,1,'2019-04-30');


INSERT INTO procurement.purchase_orders
    (order_id,
    vendor_id,
    order_date)
VALUES
    (NEXT VALUE FOR procurement.order_number,2,'2019-05-01');


INSERT INTO procurement.purchase_orders
    (order_id,
    vendor_id,
    order_date)
VALUES
    (NEXT VALUE FOR procurement.order_number,3,'2019-05-02');
Code language: SQL (Structured Query Language) (sql)

Finally, view the content of the procurement.purchase_orders table:

SELECT 
    order_id, 
    vendor_id, 
    order_date
FROM 
    procurement.purchase_orders;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

C) Using a sequence object in multiple tables example

First, create a new sequence object:

CREATE SEQUENCE procurement.receipt_no
START WITH 1
INCREMENT BY 1;
Code language: SQL (Structured Query Language) (sql)

Second, create procurement.goods_receipts and procurement.invoice_receipts tables:

CREATE TABLE procurement.goods_receipts
(
    receipt_id   INT	PRIMARY KEY 
        DEFAULT (NEXT VALUE FOR procurement.receipt_no), 
    order_id     INT NOT NULL, 
    full_receipt BIT NOT NULL,
    receipt_date DATE NOT NULL,
    note NVARCHAR(100),
);


CREATE TABLE procurement.invoice_receipts
(
    receipt_id   INT PRIMARY KEY
        DEFAULT (NEXT VALUE FOR procurement.receipt_no), 
    order_id     INT NOT NULL, 
    is_late      BIT NOT NULL,
    receipt_date DATE NOT NULL,
    note NVARCHAR(100)
);
Code language: SQL (Structured Query Language) (sql)

Note that both tables have the receipt_id whose values are derived from the procurement.receipt_no sequence.

Third, insert some rows into both tables without supplying the values for the receipt_id columns:

INSERT INTO procurement.goods_receipts(
    order_id, 
    full_receipt,
    receipt_date,
    note
)
VALUES(
    1,
    1,
    '2019-05-12',
    'Goods receipt completed at warehouse'
);
INSERT INTO procurement.goods_receipts(
    order_id, 
    full_receipt,
    receipt_date,
    note
)
VALUES(
    1,
    0,
    '2019-05-12',
    'Goods receipt has not completed at warehouse'
);

INSERT INTO procurement.invoice_receipts(
    order_id, 
    is_late,
    receipt_date,
    note
)
VALUES(
    1,
    0,
    '2019-05-13',
    'Invoice duly received'
);
INSERT INTO procurement.invoice_receipts(
    order_id, 
    is_late,
    receipt_date,
    note
)
VALUES(
    2,
    0,
    '2019-05-15',
    'Invoice duly received'
);
Code language: SQL (Structured Query Language) (sql)

Fourth, query data from both tables:

SELECT * FROM procurement.goods_receipts;
SELECT * FROM procurement.invoice_receipts;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

Sequence vs. Identity columns

Sequences, different from the identity columns, are not associated with a table. The relationship between the sequence and the table is controlled by applications. In addition, a sequence can be shared across multiple tables.

The following table illustrates the main differences between sequences and identity columns:

When to use sequences

You use a sequence object instead of an identity column in the following cases:

• The application requires a number before inserting values into the table.
• The application requires sharing a sequence of numbers across multiple tables or multiple columns within the same table.
• The application requires to restart the number when a specified value is reached.
• The application requires multiple numbers to be assigned at the same time. Note that you can call the stored procedure sp_sequence_get_range to retrieve several numbers in a sequence at once.
• The application needs to change the specification of the sequence like maximum value.

Getting sequences information

You use the view sys.sequences to get the detailed information of sequences.

SELECT 
    * 
FROM 
    sys.sequences;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned about the SQL Server sequences to generate a sequence of numbers by a specified specification.