SEQUENCE in SQL Server 2012

Introduction

SQL Server 2012 comes with many features, one of which is SEQUENCE. SEQUENCE works similarly to the IDENTITY value but the IDENTITY column has limited scope, for examle it is applied to a specific column and a specific table and it does the control by the application code. The SEQUENCE object is not limited to a column or table but it is scoped to an entire database and it is controlled by application code.

Syntax

CREATE SEQUENCE [schema name . ] sequence name
[ AS [ built in integer type | user-defined integer type ] ]
[ START WITH integer constant]
[ INCREMENT BY integer constant ]
[ { MINVALUE [integer constant] } | { NO MINVALUE } ]
[ { MAXVALUE [integer constant] } | { NO MAXVALUE } ]
[ CYCLE | { NO CYCLE } ]
[ { CACHE [integer constant] } | { NO CACHE } ]
[ ; ]

Parameters/Arguments description

• Schema name: Name of the schema in which a sequence is to be created.
• Sequence name: name of the sequence
• Built in integer type | user-defined integer type: Return types of sequence. Tinyint, smallint, int, bigint, decimal and numeric (with a scale of 0) are allowed. BigInt is the default type.
• Start with: Initial value that is returned by a sequence object. The start value must be greater than or equal to minimum value and less than or equal to maximum value of the sequence object. The default start value of a sequence is the minimum value for an ascending sequence object and the maximum value for a descending sequence object.
• Increment by: Value to be used for incrementing or decrementing the sequence object when the NEXT VALUE FOR function is called.
• Minvalue: The minimum value of the sequence object.
• Maxvalue: The maximum value of the sequence object.
• Cycle / No Cycle: This is the property foer specifying the behavior of the sequence object. If this property is set to "CYCLE" then the sequence object should restart from the minimum value (or maximum value for a descending sequence object) when the sequence reaches the maximum value (or minimum value for a descending sequence object). If this property is set to "NOCYCLE" then the sequence object throws an exception. “NOCYCLE” is the default value of the property.
• Cache / No Cache: Used to specifies pre-allocate the number of sequences by the given value.

Sequence objects are generated outside of the current transaction. If we set the cache argument to NOCACHE then SQL Server writes the value of the sequence to the system table every time the NEXT VALUE FOR function is called for the sequence object.

The following are the advantages of the Sequence object:

• Sequence object is used to generate database wide Sequence number.
• It is not associated with any specific table.
• Same Sequence object can be used with in multiple tables.

Example

In this example, I have created one Sequence object and created two tables that use the Sequence.

CREATE SEQUENCE [dbo].[TableNextId]
AS [int]
START WITH 1000
INCREMENT BY 1
MINVALUE 1000
MAXVALUE 10000
CYCLE
CACHE

CREATE TABLE TableA
(
Id INT NOT NULL,
Code VARCHAR(20),
Name VARCHAR(20)
)

CREATE TABLE TableB
(
Id INT NOT NULL,
Code VARCHAR(20),
Name VARCHAR(20)
)

INSERT INTO TableA VALUES (NEXT VALUE FOR DBO.TableNextId,'Test code table A', 'Test name table A')
INSERT INTO TableB VALUES (NEXT VALUE FOR DBO.TableNextId,'Test code table B', 'Test name table B')
INSERT INTO TableA VALUES (NEXT VALUE FOR DBO.TableNextId,'Test code table A', 'Test name table A')
INSERT INTO TableB VALUES (NEXT VALUE FOR DBO.TableNextId,'Test code table B', 'Test name table B'

Alter and Drop the Sequence object

We can also alter and drop the existing sequence. We can alter the increment, cycle, minimum value and maximum value of a sequence. Start with cannot be used with the alter statement.

ALTER SEQUENCE TableNextId
INCREMENT BY 2
MINVALUE 1000
MAXVALUE 100000
DROP SEQUENCE TableNextId

DROP SEQUENCE TableNextId

Where can we find existing Sequence object in Database? We have the following two ways:

1. The system table “Sys.Sequences” contains all the sequences that are created on the database.
   
   
   
   
2. From the Management Studio.
   
   
   

"Database" >> "Programmability" >> "Sequences".

Conclusion

Using a Sequence Object we can generate a sequence number and this Sequence can be share with multiple tables. It is similar to IDENTITY but has a greater scope.