Sequence Objects in SQL Server

Article

Sequence Objects

Sequence is a type of user-defined object that generates a sequence of numeric values. We can use a Sequence Object for T-SQL queries. A Sequence Object starts with a specific value. The value of a Sequence Object can be increase or decrease with a specific interval. Sequence Objects have an option for a minimum and maximum value. Applications refer to a Sequence Object to retrieve its next value. The relationship between sequences and tables is controlled by the application. Sequences, unlike identity columns, are not associated with specific tables.

Syntax

CREATE SEQUENCE [schema_name . ] sequence_name
[ 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 } ]
[ ; ]

Arguments

sequence_name: define a unique name for a Sequence Object.

[ built_in_integer_type | user-defined_integer_type ]: defines the data type for a Sequence Object. A sequence can be defined as any integer type. If no data type is provided, the bigint data type is used as the default. The following types are allowed:

Data Type	Range
Tinyint	0 to 255
Smallint	-32,768 to 32,767
Int	-2,147,483,648 to 2,147,483,647
Bigint	-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

START WITH: It is a type of constant. The first value returned by the Sequence Object. The START value must be a value less than or equal to the maximum and greater than or equal to the minimum value of the Sequence Object.

INCREMENT BY: It is a type of constant. It can be either a positive or negative value. If value is a negative number then the value will be an ascending sequence else the value will be an descending sequence.

MINVALUE: It defines a minimum value allowed for the sequence. The default minimum value for a new Sequence Object is the minimum value of the data type of the Sequence Object.

MAXVALUE: It defines a maximum value allowed for the sequence. The default maximum value for a new Sequence Object is the maximum value of the data type of the Sequence Object.

[ CYCLE | NO CYCLE ]: Cycle means that the sequence will start over once it has completed the sequence. NO CYCLE means that the sequence will raise an error when it has completed the sequence. It will not start the sequence over again.

[ CACHE [<constant> ] | NO CACHE ]: Increases performance for applications that use Sequence Objects by minimizing the number of disk operations required to generate sequence numbers. Defaults to CACHE.

For example, if a cache size of 50 is chosen, SQL Server does not keep 50 individual values cached. It only caches the current value and the number of values left in the cache. This means that the amount of memory required to store the cache is always two instances of the data type of the Sequence Object.

Now for a better understanding we will consider some examples.

Example 1

CREATE SEQUENCE Sequence_Example
START WITH 1
INCREMENT BY 1;

In the preceding example, we created a sequence with the name Sequemce_Example. In this example we don't define the data type for the sequence. So SQL Server uses the default datatype (in other words bigint). The starting value of the sequence is 1 . We don't define the minimum and maximum value for Sequence, so SQL Server will use the minimum and maximum value of datatype (in other words minimum = -9,223,372,036,854,775,808 , maximum = 9,223,372,036,854,775,807). We don't define anything about the Cache Option, so SQL Server uses the Cache option by default for the sequence.

Example 2

CREATE SEQUENCE Sequence_Examle
AS SMALLINT
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 99
NO CYCLE
CACHE 10;

In the preceding example, we created a sequence with the name Sequence_Example. The data type of the sequence is a smallint. Sequence will start from 1 and each time it increases by 1 (in other words 2, 3, 4, 5…). Minimum value of sequence is 1 and maximum value is 99.

We define the cache size of 10, so it will cache the 10 value at a time to increase the performance. We use the No CYCLE option for sequence so the sequence will truncate after obtaining the maximum value.

Example 3

CREATE SEQUENCE Sequence_Example
AS SMALLINT
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 99
NO CYCLE
CACHE 10;
SELECT NEXT VALUE FOR Sequence_Example AS [sequence]

Output

see result

The “SELECT NEXT VALUE FOR Sequence_Object” statement is used for retrieving the next value from the Sequence Object.

Example 4

CREATE SEQUENCE Sequence_Example
AS SMALLINT
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 99
NO CYCLE
CACHE 10;
SELECT NEXT VALUE FOR Sequence_Example AS [sequence] UNION ALL
SELECT NEXT VALUE FOR Sequence_Example UNION ALL
SELECT NEXT VALUE FOR Sequence_Example UNION ALL
SELECT NEXT VALUE FOR Sequence_Example

Output

message

This example illustrates that we cannot use Distinct, Union, Union All, Intersect and Except operator with a Sequence Object.

Example 5

CREATE TABLE #TEMP
(
IID INT IDENTITY(1,1),
SEQUENCE INT
);
DECLARE @INT INT;
SET @INT=1;
WHILE(@INT<=10)
BEGIN
INSERT INTO #TEMP
VALUES
(
NEXT VALUE FOR Sequence_Example
)
SET @INT=@INT+1;
END
SELECT * FROM #TEMP t
DROP TABLE #TEMP

Output

TEMP

This example shows how to insert the data into a table from a Sequence Object.

Example 6: Drop Sequence

IF EXISTS (SELECT * FROM sys.sequences WHERE name = N'Sequence_Example')
DROP SEQUENCE Sequence_Example;

This example shows that we can drop a Sequence Object using the Drop Command.

Example 7

CREATE SEQUENCE Sequence_Example
AS SMALLINT
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 5
NO CYCLE
CACHE 5;
CREATE TABLE #TEMP
(
IID INT IDENTITY(1,1),
SEQUENCE INT
);
DECLARE @INT INT;
SET @INT=1;
WHILE(@INT<=10)
BEGIN
INSERT INTO #TEMP
VALUES
(
NEXT VALUE FOR Sequence_Example
)
SET @INT=@INT+1;
END
SELECT * FROM #TEMP t
DROP TABLE #TEMP

Output

result

If we execute the preceding code, then SQL Server will throw an error. Because a Sequence Object has a maximum value 5 and we are exceeding this value.

So, to overcome this problem we can use the Cycle option for a Sequence Object.

Example 8 (Sequence with Cycle Option)

CREATE SEQUENCE Sequence_Example
AS SMALLINT
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 5
CYCLE
CACHE 5;
CREATE TABLE #TEMP
(
IID INT IDENTITY(1,1),
SEQUENCE INT
);
DECLARE @INT INT;
SET @INT=1;
WHILE(@INT<=10)
BEGIN
INSERT INTO #TEMP
VALUES
(
NEXT VALUE FOR Sequence_Example
)
SET @INT=@INT+1;
END
SELECT * FROM #TEMP t
DROP TABLE #TEMP

Output

SELECT

In this example we define the maximum value equal to 5 and the Sequence Object contains a Cycle option so the Sequence Object will restart after reaching the maximum value and the values will repeat after a definite interval.

Example 9 (Restart Sequence)

IF EXISTS (SELECT * FROM sys.sequences WHERE name = N'Sequence_Example')
DROP SEQUENCE Sequence_Example;
CREATE SEQUENCE Sequence_Example
AS SMALLINT
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 5
CYCLE
CACHE 5;
CREATE TABLE #TEMP
(
IID INT IDENTITY(1,1),
SEQUENCE INT
);
DECLARE @INT INT;
SET @INT=1;
WHILE(@INT<=10)
BEGIN
IF @INT%4=0
BEGIN
ALTER SEQUENCE Sequence_Example /* Restart Sequence */
RESTART;
END
INSERT INTO #TEMP
VALUES
(
NEXT VALUE FOR Sequence_Example
)
SET @INT=@INT+1;
END
SELECT * FROM #TEMP t
DROP TABLE #TEMP

Output

Restart Sequence

This example shows that we can restart a Sequence Object at any time. To restart the current value of a Sequence Object to its initial or specified value, use the ALTER SEQUENCE statement.

Example 10

IF EXISTS (SELECT * FROM sys.sequences WHERE name = N'Sequence_Example')
DROP SEQUENCE Sequence_Example;
CREATE SEQUENCE Sequence_Example
AS SMALLINT
START WITH 1
INCREMENT BY 5
MINVALUE 1
MAXVALUE 50
CYCLE
CACHE 5;
CREATE TABLE #TEMP
(
IID INT IDENTITY(1,1),
SEQUENCE INT
);
DECLARE @INT INT;
SET @INT=1;
WHILE(@INT<=10)
BEGIN
IF @INT%4=0
BEGIN
ALTER SEQUENCE Sequence_Example /* Restart with a specific value */
RESTART WITH 10 ;
END
INSERT INTO #TEMP
VALUES
(
NEXT VALUE FOR Sequence_Example
)
SET @INT=@INT+1;
END
SELECT * FROM #TEMP t
DROP TABLE #TEMP

Output

LTER SEQUENCE

This example shows that we can restart any Sequence Object with a specific value. In the preceding example we can see that when the Sequence Object restarts, it's start value will be 10 each time.

Example 11 (Sequence with descending order)

IF EXISTS (SELECT * FROM sys.sequences WHERE name = N'Sequence_Example')
DROP SEQUENCE Sequence_Example;
CREATE SEQUENCE Sequence_Example
AS SMALLINT
START WITH 10
INCREMENT BY -1
MINVALUE 1
MAXVALUE 50
CYCLE
CACHE 5;
CREATE TABLE #TEMP
(
IID INT IDENTITY(1,1),
SEQUENCE INT
);
DECLARE @INT INT;
SET @INT=1;
WHILE(@INT<=10)
BEGIN
INSERT INTO #TEMP
VALUES
(
NEXT VALUE FOR Sequence_Example
)
SET @INT=@INT+1;
END
SELECT * FROM #TEMP t
DROP TABLE #TEMP

Output

DROP TABLE

Example 12 (Sequence Information)

Using system view sys.sequences we can retrieve information about any sequence. A sys.sequences view can obtain the following information about the Sequence Object.