Sequence in SQL Server 2012
One of the new
features introduced in SQL Server 2012 is Sequence. A sequence is a
user-defined schema-bound object that generates a sequence of numeric values
according to the specification with which it was created.
I know, the next
question which would come to your mind is it’s similar to the Identity column?
Well, it is
similar but not the same. There are differences. In order to decide whether it
makes sense to use Sequence in a specific scenario, let’s understand these
differences.
Identity Column
|
Sequence
|
1.
Is bound to the table
|
Is
not bound to the table
|
2.
Only column can be defined as
Identity Column
|
Can
be used with multiple columns and in multiple tables
|
3.
Is not an ANSI standard
|
Is
an ANSI standard
|
4.
No option available to alter
the properties of an Identity Column
|
ALTER
SEQUENCE statement can be used to alter the properties of the Sequence
|
5.
Application cannot control
the Identity Column
|
Application
can control the Identity Column
|
Let’s take a look at the Demo
on Using Sequence objects.
§ Create a sequence. By default uses bigint data type.
CREATE SEQUENCE EmpSeq;
§ Find the information about the sequence just created using a system
defined view.
SELECT *
FROM sys.sequences;
§ Drop the previously created sequence.
DROP SEQUENCE EmpSeq;
§ Create a new sequence using options. Here Cache option is used to
inform SQL Server to maintain the current value in the memory. The constant
value specified after the CACHE option tells the SQL Server how often the Meta
data needs an update. Having the values in memory is more efficient rather than
each time generating the value. Here SQL Server will generate 5 values in keep
them into the memory. The meta data here will be updated when the sequence is
called 6th time.
CREATE SEQUENCE EmpSeq AS INT
START WITH 10
INCREMENT BY 10
MAXVALUE 100
CACHE 5;
§ Review properties of the sequence created.
SELECT *
FROM sys.sequences;
§ Generate the value. Repeat the same statement for 10 times so as to
reach the maximum limit.
SELECT NEXT VALUE FOR EmpSeq;
§ After the maximum value is reached, the following error would be
reported.
§ Restart the value.
ALTER SEQUENCE EmpSeq RESTART;
§ Review properties of the sequence after modification.
SELECT *
FROM sys.sequences;
§ Cycle through the values.
DROP SEQUENCE EmpSeq;
CREATE SEQUENCE EmpSeq AS int
START WITH 10
INCREMENT BY 10
MAXVALUE 100
CACHE 5
CYCLE;
§ Check is_cycle property is on.
SELECT *
FROM sys.sequences;
§ Use sequence to insert values in the table.
CREATE TABLE Employees
(EmpID int,
FirstName varchar(20),
LastName varchar(20)
);
INSERT INTO Employees
VALUES(NEXT VALUE FOR EmpSeq, 'Steven', 'King'),
(NEXT VALUE FOR EmpSeq, 'Neena',
'Kochhar'),
(NEXT VALUE FOR EmpSeq, 'Lex', 'Dehaan'),
(NEXT VALUE FOR EmpSeq, 'Andrew', 'Larson'),
(NEXT VALUE FOR EmpSeq, 'Sarah', 'Grant'),
(NEXT VALUE FOR EmpSeq, 'Nancy', 'Greenberg'),
(NEXT VALUE FOR EmpSeq, 'Kimberley', 'Grant'),
(NEXT VALUE FOR EmpSeq, 'Janet', 'King'),
(NEXT VALUE FOR EmpSeq, 'Annie', 'Moses'),
(NEXT VALUE FOR EmpSeq, 'Mike', 'Grant'),
(NEXT VALUE FOR EmpSeq, 'Tom', 'Cruise'),
(NEXT VALUE FOR EmpSeq, 'Angela', 'Jolie')
§ Verify data.
SELECT *
FROM Employees;
