Sequence in SQL Server 2012

In this blog, I will give you a walk through about a new feature introduced in sql server 2012 – Sequence.

What is a Sequence?

It is basically a user defined object that generates numeric values in sequential order. User can define the order in which the sequence has to be generated. It can be in ascending or descending order. One major difference with identity column is that sequence is not associated with a particular table. The application has to maintain the relationship between sequence and table.

Syntax is as given below -:

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

By default, the sequence accepts bigint as datatype. The datatype can be a built in (eg-: tinyint , smallint , etc) or user defined one.

The START value must be assigned with a value less than or equal to the maximum and greater than or equal to the minimum value of the sequence object.

The INCREMENT value is by default 1. It cannot be set to 0. If the values specified is negative, then sequence returns numbers in descending order else vice-versa.

The MINVALUE value specifies the lower limit of sequence. By default it is the minimum value of the data type.

The MAXVALUE value specifies the upper limit of sequence. By default it is the maximum value of the data type.

The CYCLE|NOCYCLE option specifies whether the sequence has to be restart from MINVALUE/MAXVALUE. By default the value is NOCYCLE.

The sequence created will be listed under Sequence folder as shown below. From the Management studio >> Databasenode >> Programmability >> Sequences.

How to create a sequence using script?

In the below script a sequence named IncrementBy1 is created that increases by one every time that it is used. This is an example for incrementing sequence.

CREATE SEQUENCE IncrementBy1 START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 200 CYCLE;

The below script is an example for a sequence in descending order.

CREATE SEQUENCE DecrementBy1 START WITH 100 INCREMENT BY -1;

How to create a sequence using Management Studio?

From management studio, by right clicking on Sequence, a popup is provided where user can create a new sequence.

Type in your sequence name, start value, Increment by. The Minimum /Maximum values are taken default. The data type of sequence is populated by default. Press the OK button to create the sequence.

Uses

We can use sequence in the following scenarios

a) Application needs a unique number before the insertion is made.

b) Application needs to share unique number among multiple tables or multiple columns within a table.

c) With Cycle option, we can restart the number series when specified number is reached thereby allowing repetition.

References

http://msdn.microsoft.com/en-us/library/ff878091.aspx

Summary

In this blog we had a look at how to create a sequence and its syntax. In next article, we will look more into detail about how to use the sequence and other associated items.