Difference between sequence and identity in SQL Server

Difference Between Sequence and Identity in SQL Server

In this article, I am going to discuss the difference between sequence and identity in SQL Server with examples. I strongly recommended you to read the following two articles before proceeding to this article where we discussed Identity and Sequence in SQL Server with examples.

The Identity in SQL Server

The sequence in SQL Server

The difference between sequence and identity in SQL Server is one of the most frequently asked interview questions. So let start the discussion.

The sequence object is very much similar to the Identity property in SQL Server, in the sense that it generates a sequence of numeric values in an ascending order just like the identity property. However, there are several differences between them.  In this article, we will discuss the Difference between sequence and identity in SQL Server.

Identity property is a table column property meaning it is tied to the table, whereas the sequence is a user-defined database object and is not tied to any specific table meaning its value can be shared by multiple tables.

Example:

In the below example the Identity property tied to the Id column of the Employees table. 

CREATE TABLE Employees
(
    Id INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(50),
    Gender NVARCHAR(10)
)
Example: 

The sequence object is not tied to any specific table

CREATE SEQUENCE [dbo].[SequenceObject]
AS INT
START WITH 1
INCREMENT BY 1

That means the above sequence object can be used with any table.

Sharing sequence object value with multiple tables.

Example: 

Step 1: Create Customers and Users tables

CREATE TABLE Customers
(
    CustomerId INT PRIMARY KEY,
    CustomerName NVARCHAR(50),
    Gender NVARCHAR(10)
)

CREATE TABLE Users
(
    UserId INT PRIMARY KEY,
    UserName NVARCHAR(50),
    Gender NVARCHAR(10)
)

Step 2: Please use below SQL script to Insert two rows into the Customers table and three rows into the Users table. Please notice that the same sequence object is used to generating the ID values of both the Customers and Users tables.

INSERT INTO Customers VALUES
   (NEXT VALUE for [dbo].[SequenceObject], 'Pranaya', 'Male')
INSERT INTO Customers VALUES
   (NEXT VALUE for [dbo].[SequenceObject], 'Mitali', 'Female')

INSERT INTO Users VALUES
   (NEXT VALUE for [dbo].[SequenceObject], 'Asish', 'Male')
INSERT INTO Users VALUES
   (NEXT VALUE for [dbo].[SequenceObject], 'Priyanka', 'Female')
INSERT INTO Users VALUES
   (NEXT VALUE for [dbo].[SequenceObject], 'Sambit', 'Male')

Step 3: Query both the tables like below

SELECT * FROM Customers

difference between sequence and identity in SQL Server

SELECT * FROM Users

difference between sequence and identity in SQL Server

Notice the same sequence object has generated the values for ID columns (CustomerId and UserId) in both the tables 

In case of Identity to generate the next identity value, a row has to be inserted into the table, whereas in case of sequence object we don’t require to add a row into the table in order to generate the next sequence value. You can use the NEXT VALUE FOR clause to generate the next sequence value in SQL Server.

Example: Generating the Identity Column value by inserting a row into the table

INSERT INTO Employees VALUES (‘Anurag’, ‘Male’)

Example: Generating the sequence object value by using the NEXT VALUE FOR clause.

SELECT NEXT VALUE FOR [dbo].[SequenceObject]

The maximum value for the identity property cannot be specified. In the case of Identity, the maximum value will be the maximum value of the corresponding column data type on which the Identity property is specified. In the case of the sequence object, we can use the MAXVALUE option to specify the maximum value. If the MAXVALUE option is not specified for the sequence object, then the maximum value will be the maximum value of its data type.

Example: Specifying the maximum value for the sequence object using the MAXVALUE option
CREATE SEQUENCE [dbo].[SequenceObject]
START WITH 1
INCREMENT BY 5
MAXVALUE 50
CYCLE option

In the case of Sequence Object, you can use the CYCLE option to specify whether the sequence should restart automatically when the sequence object value reached the max or min value, whereas in case of Identity property we don’t have such option to automatically restart the identity values.

Example: Let us specifying the CYCLE option of the Sequence object so that the sequence object value will restart automatically when the max value is reached.

CREATE SEQUENCE [dbo].[SequenceObject]
START WITH 1
INCREMENT BY 5
MINVALUE 1
MAXVALUE 50
CYCLE
So in short

The Identity property of a column is available from SQL Server 2000 whereas Sequence object is available from SQL Server 2012.

Identity cannot be controlled by application code whereas Sequence can be controlled by application code.

If any column is marked as an Identity then we cannot insert data into this column directly. In that case, we must first turn off the Identity of the column whereas a Sequence object does not depend on the table so we can insert any value in the column.

We cannot get the value of an Identity column before inserting a record whereas we can get the value of the next Sequence number for a Sequence Object before inserting a record.

We can reseed an Identity property but we cannot change the step size whereas we can alter the seed well as the Step size of a Sequence object at any time.

The sequence is used to generate a database-wide sequential number whereas the identity column is tied to a table.

In the next article, I will discuss the Select Statement in SQL Server.

SUMMARY

In this article, I try to explain the difference between sequence and identity in SQL Server in SQL Server step by step with a simple example. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article. 

Leave a Reply

Your email address will not be published. Required fields are marked *