SQL SERVER

Sequence object in SQL Server 2012

Sequence object in SQL Server 2012

what is sql sequence?

A sequence is a user-defined schema bound object that generates a sequence of numeric values according to the specification with which the sequence was created. The sequence of numeric values is generated in an ascending or descending order at a defined interval and can be configured to restart (cycle) when exhausted.

Sequence object

  • Introduced in SQL Server 2012
  • Generates sequence of numeric values in an ascending or descending order

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

PropertyDescription
DataTypeBuilt-in integer type (tinyint , smallint, int, bigint, decimal etc…) or user-defined integer type. Default bigint.
START WITHThe first value returned by the sequence object
INCREMENT BYThe value to increment or decrement by. The value will be decremented if a negative value is specified.
MINVALUEMinimum value for the sequence object
MAXVALUEMaximum value for the sequence object
CYCLESpecifies whether the sequence object should restart when the max value (for incrementing sequence object) or min value (for decrementing sequence object) is reached. Default is NO CYCLE, which throws an error when minimum or maximum value is exceeded.
CACHECache sequence values for performance. Default value is CACHE.

Creating an Incrementing Sequence : The following code create a Sequence object that starts with 1 and increments by 1

CREATE SEQUENCE [dbo].[SequenceObject] 
AS INT
START WITH 1
INCREMENT BY 1

Generating the Next Sequence Value : Now we have a sequence object created. To generate the sequence value use NEXT VALUE FOR clause

SELECT NEXT VALUE FOR [dbo].[SequenceObject]

Output : 1

Every time you execute the above query the sequence value will be incremented by 1. I executed the above query 5 times, so the current sequence value is 5.

Retrieving the current sequence value : If you want to see what the current Sequence value before generating the next, use sys.sequences

SELECT * FROM sys.sequences WHERE name = ‘SequenceObject’

Alter the Sequence object to reset the sequence value : 
ALTER SEQUENCE [SequenceObject] RESTART WITH 1

Select the next sequence value to make sure the value starts from 1
SELECT NEXT VALUE FOR [dbo].[SequenceObject]

Using sequence value in an INSERT query : 

CREATE TABLE Employees (     Id INT PRIMARY KEY,     Name NVARCHAR(50),     Gender NVARCHAR(10) )

— Generate and insert Sequence values INSERT INTO Employees VALUES (NEXT VALUE for [dbo].[SequenceObject], ‘Ben’, ‘Male’) INSERT INTO Employees VALUES (NEXT VALUE for [dbo].[SequenceObject], ‘Sara’, ‘Female’)

— Select the data from the table

SELECT * FROM Employees

sequence object in sql server 2012

Creating the decrementing Sequence : The following code create a Sequence object that starts with 100 and decrements by 1

CREATE SEQUENCE [dbo].[SequenceObject] 
AS INT
START WITH 100
INCREMENT BY -1

Specifying MIN and MAX values for the sequence : Use the MINVALUE and MAXVALUE arguments to specify the MIN and MAX values respectively.

Step 1 : Create the Sequence object
CREATE SEQUENCE [dbo].[SequenceObject]
START WITH 100
INCREMENT BY 10
MINVALUE 100
MAXVALUE 150

Step 2 : Retrieve the next sequence value. The sequence value starts at 100. Every time we call NEXT VALUE, the value will be incremented by 10. 

SELECT NEXT VALUE FOR [dbo].[SequenceObject]

Recycling Sequence values : When the sequence object has reached it’s maximum value, and if you want to restart from the minimum value, set CYCLE option

ALTER SEQUENCE [dbo].[SequenceObject]
INCREMENT BY 10
MINVALUE 100
MAXVALUE 150
CYCLE

At this point, whe the sequence object has reached it’s maximum value, and if you ask for the NEXT VALUE, sequence object starts from the minimum value again which in this case is 100.

To improve performance, the Sequence object values can be cached using the CACHE option. When the values are cached they are read from the memory instead of from the disk, which improves the performance. When the cache option is specified you can also specify the size of th cache , that is the number of values to cache.

The following example, creates the sequence object with 10 values cached. When the 11th value is requested, the next 10 values will be cached again.

CREATE SEQUENCE [dbo].[SequenceObject]
START WITH 1
INCREMENT BY 1
CACHE 10

Using SQL Server Graphical User Interface (GUI) to create the sequence object : 
1. Expand the database folder
2. Expand Programmability folder
3. Right click on Sequences folder
4. Select New Sequence

create sequence in sql server

About the author

shohal

Leave a Comment