This article will explain the use of Sequence in SQL server and its advantages over an Identity column.
Introduction:
Sequence in SQL server is a new database object and a substitute for the
Identity columns.
Using the identity attribute for a column, we can easily generate
auto-incrementing numbers, which are often used as a primary key. With Sequence,
it will be a different object which you can attach to a table column while
inserting. Unlike identity, the next number for the column value will be
retrieved from memory rather than from the disk – this makes Sequence
significantly faster than Identity, and which is the main advantage of Sequence.
We can see Sequence in object explorer at the below path
In object explorer, Databases-> particular database->Programmability->Sequences.
Explanation:
Sequence is a database object; it needs to be assigned to a schema. It has a
data type which can be int, bigint, tinyint, smallint, numeric or decimal. The
start value and increment as similar as to the values you will be familiar with
using Identity.
Below is query to create sequence
CREATE SEQUENCE StudentIDSeq
AS int
START WITH 1
INCREMENT BY 1;
Now let's see how to use sequence in an Insert statement.
First we will create a table to use the sequence we have created.
CREATE TABLE
StudentData
(ID int,
Name varchar(150)
)
Now let's insert to it using sequence:
INSERT
INTO StudentData (ID,Name)
VALUES
(NEXT VALUE
FOR StudentIDSeq,
'Pravin')
INSERT INTO
StudentData(ID,Name)
VALUES (NEXT
VALUE FOR StudentIDSeq,
'Navjyot)
So result of select * from StudentData is below
ID Name
- Pravin
- Navjyot
Reseed Sequence:
We can restart sequence like we reseed identity column.
ALTER SEQUENCE dbo.
StudentIDSeq
RESTART WITH 2;
The above statement will allow you to restart the sequence from 2.
Note: One more point I would like to mention here is Sequence doesn't
rollback its value if transaction in which it's used is rollback.
Reuse Sequence value using CYCLE option:
If you have set the cycle option on, your Sequence object will re-use numbers.
Let us see this in an example. By stating CYCLE your sequence cycle option will
be set to true.
CREATE
SEQUENCE [dbo].[StudentIDSeq]
AS [tinyint]
START WITH 2
INCREMENT BY 5
CYCLE
After 255 sequence will again start with 2.
Performance wise Sequence has advantage over the identity but it has disadvantages too.
Disadvantages:
- Allows to use NEXT VALUE FOR in a UNION ALL, but not in a UNION, EXCEPT, INTERSECT, or with DISTINCT.
- NEXT VALUE FOR function is not allowed in check constraints, default objects, computed columns, views, user-defined functions, user-defined aggregates, sub-queries, common table expressions, or derived tables