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.