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 Identity in SQL Server and Sequence in SQL Server articles before proceeding to this article where we discussed Identity and Sequence in SQL Server with examples. The difference between sequence and identity in SQL Server is one of the most frequently asked interview questions.
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. So let start the discussion.
What is the Difference Between Sequence and Identity in SQL Server?
The Identity property is a column property meaning it is tied to the table, whereas the sequence is a user-defined database object and it is not tied to any specific table meaning its value can be shared by multiple tables.
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) )
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.
Example of Sharing sequence object value with multiple tables in SQL Server:
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')
Step3: Query both the tables like below
SELECT * FROM Customers
SELECT * FROM Users
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
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 difference between sequence and identity in SQL Server are as follows,
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 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 as 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 am going to discuss the Select Statement in SQL Server with examples. Here, in this article, I try to explain the difference between sequence and identity in SQL Server step by step with a simple example.