SQL Server Sequence

Learning Objective

The objective of this tutorial is to teach you how to use the Sequence object in SQL Server to generate a sequence of numeric values in a particular order. 

What is a Sequence

A Sequence is a list of number which is in particular order. In Sequence, ordering of the number is important. For example, the (1,2,3,4) is a sequence however the (4,3,2,1) is a entirely difference sequence although all the numbers in both the Sequence are same.

In SQL Server, a sequence is a user-defined object that generates a sequence of numbers in a particular order. 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 syntax

CREATE SEQUENCE allows you to create a new sequence in SQL Server. The syntax for creating a new sequence is shown below:

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 } ];

In this syntax,

• schema_name – Name of the schema where the sequence belongs.
• sequence_name – Name of the sequence you wish to keep.
• AS integer_type – You can use any valid inter type for sequence. For example, TINYINT, SMALLINT, INT, BIGINT, DECIMAL or NUMERIC with a scale of 0. Default is BIGINT.
• START WITH start_value – Defines the start value of the sequence. The start value must be within the range of min_value and max_value. The default value of start_value is min_value for an ascending sequence and max_value for a descending sequence.
• INCREMENT BY increment_value – Defines the increment value of the sequence object when you call the NEXT VALUE FOR function. In case the increment_value is negative then it is a descending sequence otherwise ascending sequence. Remember that increment_value can’t be 0.
• [ { MINVALUE [ min_value ] } | { NO MINVALUE } ] – Defines the lower bound of the sequence objects. In case of TINYINT, default value is 0 and for all other datatypes it is negative.
• [ { MAXVALUE [ max_value ] } | { NO MAXVALUE } ] – Defines the upper bound of the sequence object. Default is maximum range of the data types of the sequence objects.
• [ CYCLE | { NO CYCLE } ] – You can use the CYCLE clause if you want the value of the sequence to be restart from the min_value for the ascending sequence object and max_value for the descending sequence object or throw an exception when the range of min_value and max_value exceeds. By default, SQL Server uses NO CYCLE for new sequence objects.
• [ { CACHE [ cache_size ] } | { NO CACHE } ] – This is used to define the number of sequence values to cache to improve the performance of the sequence by reducing the numbers of disk I/O. By default, SQL Server uses NO CACHE for a new sequence objects.

SQL Server SEQUENCE Examples

To understand the concept of sequence with more clarity, let’s check some examples of sequence.

1) Creating a simple sequence

You can create a new sequence by using CREATE SEQUENCE statement. In this example, we have created a sequence named product_id with start value 100 and increment value 10 and the data type is integer (INT).

CREATE SEQUENCE product_id
AS INT
START WITH 100
INCREMENT BY 10;

You can also view the sequence objects in SQL Server Management Studio by expanding Programmability > Sequences as shown below:

The below statement will return the current value of the sequence object.

SELECT NEXT VALUE FOR product_id;

Output

Current_value
100
(1 row affected)

In the above example, NEXT VALUE FOR generates a sequence for the object product_id.

Each time you executes the above statement, the sequence will increment by 10.

SELECT NEXT VALUE FOR product_id;

This time, the above statement will generate the output 110 as below.

Output

Current_value
110
(1 row affected)

2) Using a Sequence in a Single Table

First, create a new schema generalstore as follows.

CREATE SCHEMA generalstore;

Next, create a new table named product in genrealstore schema.

CREATE TABLE generalstore.product
(
product_id INT PRIMARY KEY,
product_name nvarchar(100) NOT NULL,
quantity INT NOT NULL
);

Now, create a sequence name product_id_seq as below.

CREATE SEQUENCE product_id_seq
AS INT
START WITH 100
INCREMENT BY 1;

After that, insert three rows into the generalstore.product table. We will use product_id_seq for inserting new product if into the table.

INSERT INTO generalstore.product
(product_id,
product_name,
quantity)
VALUES
(NEXT VALUE FOR product_id_seq,'Milk',20);

INSERT INTO generalstore.product
(product_id,
product_name,
quantity)
VALUES
(NEXT VALUE FOR product_id_seq,'Bread',25);

INSERT INTO generalstore.product
(product_id,
product_name,
quantity)
VALUES
(NEXT VALUE FOR product_id_seq,'Eggs',160);

Finally, you can check the content of the product table using select query.

select * from generalstore.product;

You can notice that the product_id column has been inserted using the product_id_seq sequence. Here first value is 100 and incremented by 1 for each new row.