Sequence in SQL Server

Introduction

In this article i will explain about what is Sequence and how to use this in Sql Server.

What Sequence Is

SEQUENCE is a user defined schema object that create a sequence of number. In other words you can say that it is a set of of integer that are generated in according to your need. Just like - 1, 2, 3, 4......... or same in descending based on your requirement.

In past Sql servers we used Identity for the same but in SQL Server 2012 this Sequnce concept came. Sequence is very frequently used with Sql server for generating unique id's after inserting every row in any table. 

The main thing to know about Sequence is that it is table independent . Means just opposite to IDENTITY. I already explained Identity in my last update. In identity there is a dependency with table, for every new table you have to define a new auto incremented Identity column but with Sequence you just create it once in your database and use it as many times you want with different tables.

Create Sequence: 

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

START WITH	Starting number in the sequence
INCREMENT BY	The incrementing value of the sequence
MINVALUE	The maximum value the sequence can produce.
MAXVALUE	- If the MAXVALUE is set with the CYCLE, when the MAXVALUE is reached the sequence will cycle to the MINVALUE and start again.
CYCLE	The minimum value the sequence can produce.
CACHE	If a CACHE argument is provided, SQL Server will cache (store in memory) the amount of values specified.

If you worked with Oracle then you already aware with Sequence but for Sql server this is new concept just like int Identity column. But sequence is working on database level and Identity work on table level.

Working with Sequence 

CREATE SEQUENCE TestSequence     AS INT     MINVALUE 1     MAXVALUE 500     START WITH 1 GO

This sequence named 'TestSequnce' will be unique for the database. And also you can change the INT type to TINYINT, SMALLLINT, BIGINT. 

View Sequence: 

SELECT * FROM sys.sequences GO

After creating your sequence you can verify or check how it works by using the following line of code

DECLARE @id INT SET @id = NEXT VALUE FOR TestSequence SELECT @id GO

When you execute this line for the first time it gives you 1 as output but after several execution it produce result like 2,3,4,5,6...... Upto 500 which is our MaxValue.

Alter Sequence:

ALTER SEQUENCE TestSequence     RESTART WITH 15     INCREMENT BY 10     NO MAXVALUE GO

Here i did not specify the MaxValue by using NO MAXVALUE. In this case sql server automatically manage the max value for sequence according to its given DataType .

Drop Sequence:

DROP SEQUENCE TestSequence GO

By using drop you can drop your sequence and after that you are able to create the same name sequence again.