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.
