A Beginner’s Guide to Sequence Objects in SQL Server

SQL Server has supported SEQUENCE objects since the inception of SQL Server 2012 and later editions. Oracle database has supported SEQUENCE objects from the beginning. You can read the archive post on Tech-Recipes using SEQUENCE in Oracle.

We can create a SEQUENCE in SQL Server to generate sequential numeric values according to the guidelines provided. SEQUENCE objects can be classified with an IDENTITY column in SQL Server, but unlike IDENTITY columns, SEQUENCE objects are not linked to Table objects. IDENTITY is considered a table property which is attached to tables, whereas SEQUENCE objects are created independently and can be used with DML statements like INSERT and UPDATE or to refer a SEQUENCE object separately. This tutorial is a beginner’s guide to SEQUENCE objects in SQL Server.

How to Differentiate between SEQUENCE and IDENTITY Properties

1. Sequence objects are not dependent on tables, whereas Identity properties are associated with tables.

2. Sequence objects are used to generate sequential values across multiple tables with the database scope. The Identity property can be used to generate incremental numbers at the table level.

3. Sequence objects were introduced with an edition of SQL Server 2012, whereas Identity properties are supported with older editions of SQL Server.

4. We can reset a Sequence object’s current sequence number and incremental step size. With IDENTITY, we can RESEED to a specific value, but we cannot change the incremental step size.

5. Sequence objects can be cached in memory to improve performance, whereas Identity values cannot be cached.

6. With a sequence, you can define Min/Max value. With Identity property, this is not supported.

The Syntax of SEQUENCE Objects

CREATE SEQUENCE [schema].[sequence_name]
  [ AS <data type> ]
  [ START WITH <value> ]
  [ INCREMENT BY <value> ]
  [ MINVALUE <value > | NO MINVALUE ]
  [ MAXVALUE <value> | NO MAXVALUE ]
  [ CYCLE | NO CYCLE ]
  [ CACHE value | NO CACHE ];

A SEQUENCE Object’s Arguments

1. Schema: You can define a schema name while creating sequence objects. If no schema is provided, dbo schema is used by default.

2. Sequence Name : A unique name to be specified to identify sequence object

3. Data Type: Data types allowed with sequence objects are TinyInt, SmallInt, Int, Decimal and BIGINT. If no data type is provided, BIGINT data type is used by default.

4. START WITH: The first value to be returned by sequence object

5. INCREMENT BY: A value to be used to increment or decrement (if negative) the sequence object; This value cannot be 0. If the INCREMENT BY value is positive, the sequence objects are ascending. If negative, then they are descending.

6. MINVALUE – Minimum boundary value for a sequence, It’s an optional parameter, if not defined it takes minimum value of defined data type range.

7. MAXVALUE: Maximum boundary value for a sequence, It’s an optional parameter, if not defined it takes the maximum value of defined data type range.

8. CYCLE | NOCYCLE: If you want to restart a sequence object’s value from a minimum or maximum value, optional parameter, the default is NOCYCLE.

9. CACHE | NOCACHE: Increase performance by enabling cache to reduce disk I/O; using CACHE helps to read the current value from memory.

Examples: Using SEQUENCE Object with SQL Queries

1. Create a sequence object with a schema name, sequence name, data type, start with, and increment by arguments.

The following example will create a sequence object named “UniqueSequenceObject.” It will start from 1, and the value is incremented by 1 each time when we use “UniqueSequenceObject.”

IF EXISTS (SELECT name FROM Sys.sequences WHERE name = 'UniqueSequenceObject')
DROP SEQUENCE UniqueSequenceObject
GO

CREATE SEQUENCE dbo.UniqueSequenceObject
AS INT
	START WITH 1
	INCREMENT BY 1
	NO MINVALUE
	NO MAXVALUE
	NO CYCLE
	NO CACHE 
GO

2. How to Get the First Value Using a Sequence Object

The following statement will return the value “1” to get the first value using “UniqueSequenceObject.”

SELECT value = NEXT VALUE FOR dbo.UniqueSequenceObject;
     

Output 

value
-------
 1  

3. Storing a Sequence Object Value in a Variable

The following statement will declare a variable and store the next sequence value (i.e., “2”) in @SequenceValue variable.

DECLARE @SequenceValue as INT;

SET @SequenceValue = NEXT VALUE FOR dbo. UniqueSequenceObject ;
SELECT @SequenceValue as SequenceValue;

Output 

SequenceValue
--------------------
2  

4. Viewing properties of a Sequence Object using System View

The following query uses the system’s view sys.sequence to provide more information about sequence object “UniqueSequenceObject”. It shows us a start value, min/max value and a current value.

select   name, type_desc, start_value, increment, minimum_value, maximum_value, current_value
from     sys . sequences
where  name = 'UniqueSequenceObject';

The table below shows the start_value and increment value as “1.” We defined these values while creating a sequence object. It also shows a min/max value which are boundary values of the Integer data type. It is also showing a current_value “2” which we used in Example 3 to store it in a variable.

5. Creating Sequence object with Minimum/Maximum Value Boundary

The following query creates a sequence object “TestSequenceObject”. It starts with 100 and incremented by 10 with Minimum value “100” and Maximum value “150”.

CREATE SEQUENCE dbo.TestSequenceObject
AS INT
    START WITH 100 
    INCREMENT BY 10
    MINVALUE 100
    MAXVALUE 150 
    NO CACHE
    NO CYCLE
   GO  

Let’s retrieve a start value using “TestSequenceObject.”

DECLARE @SequenceValue asINT;
SET @SequenceValue =NEXT VALUE FOR dbo.TestSequenceObject ;

SELECT @SequenceValue as SequenceValue;

Output 
-------------
SequenceValue
100

By running the query below, we are going to exceed the maximum boundary value defined for the sequence object. It will raise an error indicating we have crossed the boundary value for the defined sequence object. Using a GO statement, we can run the query below “5” times.

SELECT NEXT VALUE FOR dbo.TestSequenceObject
GO 5

We get the following error once the current value reaches maximum boundary value “150”.

Msg 11728, Level 16, State 1, Line 16
The sequence object 'TestSequenceObject' has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.
** An error was encountered during execution of batch. Continuing.
Batch execution completed 5 times.

6. Creating a Sequence object with the CYCLE property

In Example 5, we did not enable the CYCLE property of the sequence object. Using this property, we can restart the Sequence’s object from its minimum value to avoid throwing the error above.

Let’s create a sequence object again with the CYCLE property and test whether it fails when it reaches the maximum boundary. Once the sequence reaches the maximum boundary value, it will again start from its minimum value defined.

IF EXISTS (SELECT name FROM Sys.sequences WHERE name = 'TestSequenceObject')
DROP SEQUENCE TestSequenceObject
CREATE SEQUENCE dbo.TestSequenceObject
AS INT

    START WITH 100
    INCREMENT BY 10
    MINVALUE 100
    MAXVALUE 150
    NO CACHE
    CYCLE
GO

SELECT Value =NEXT VALUE FOR dbo.TestSequenceObject
GO 10

7. Using a Sequence Object with an INSERT Statement

IF OBJECT_ID ('Employee', 'U') IS NOT NULL
DROP TABLE Employee;
CREATE TABLE Employee 
(
 EmployeeId INT
,Name  VARCHAR (100)
);

Insert few records in Employee table using sequence object.

INSERT INTO Employee ( EmployeeId, Name ) values (next value for dbo.TestSequenceObject, 'Vishwanath');
INSERT INTO Employee ( EmployeeId, Name ) values (next value for dbo.TestSequenceObject, 'Dalvi');

SELECT  *  
FROM   EMPLOYEE;

Output

EmployeeId  Name
----------- -------------------
1           Vishwanath
2           Dalvi  

Read more about Database and SQL programming published on Tech-Recipes.