Difference between sequence and identity in SQL Server

we will discuss the difference between SEQUENCE and IDENTITY in SQL Server

Sequence object is similar to the Identity property, in the sense that it generates sequence of numeric values in an ascending order just like the identity property. However there are several differences between the 2 which we will discuss in this video.

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

Example : 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 : Sequence object not tied to any specific table

CREATE SEQUENCE [dbo].[SequenceObject]

AS INT

START WITH 1

INCREMENT BY 1

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

Example : Sharing sequence object value with multiple tables.

Step 1 : Create Customers and Users tables

CREATE TABLE Customers

(

    Id INT PRIMARY KEY,

    Name NVARCHAR(50),

    Gender NVARCHAR(10)

)

GO

CREATE TABLE Users

(

    Id INT PRIMARY KEY,

    Name NVARCHAR(50),

    Gender NVARCHAR(10)

)

GO

Step 2 : Insert 2 rows into Customers table and 3 rows into Users table. Notice the same sequence object is generating the ID values for both the tables.

INSERT INTO Customers VALUES

   (NEXT VALUE for [dbo].[SequenceObject], 'Ben', 'Male')

INSERT INTO Customers VALUES

   (NEXT VALUE for [dbo].[SequenceObject], 'Sara', 'Female')

INSERT INTO Users VALUES

   (NEXT VALUE for [dbo].[SequenceObject], 'Tom', 'Male')

INSERT INTO Users VALUES

   (NEXT VALUE for [dbo].[SequenceObject], 'Pam', 'Female')

INSERT INTO Users VALUES

   (NEXT VALUE for [dbo].[SequenceObject], 'David', 'Male')

GO

Step 3 : Query the tables

SELECT * FROM Customers

SELECT * FROM Users

GO

Output : Notice the same sequence object has generated the values for ID columns in both the tables



To generate the next identity value, a row has to be inserted into the table, where as with sequence object there is no need to insert a row into the table to generate the next sequence value. You can use NEXT VALUE FOR clause to generate the next sequence value.

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

INSERT INTO Employees VALUES ('Todd', 'Male')

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

SELECT NEXT VALUE FOR [dbo].[SequenceObject]

Maximum value for the identity property cannot be specified. The maximum value will be the maximum value of the correspoding column data type. With the sequence object you 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 it's data type.

Example : Specifying maximum value for the sequence object using the MAXVALUE option

CREATE SEQUENCE [dbo].[SequenceObject]

START WITH 1

INCREMENT BY 1

MAXVALUE 5

CYCLE option of the Sequence object can be used to specify whether the sequence should restart automatically when the max value (for incrementing sequence object) or min value (for decrementing sequence object) is reached, where as with the Identity property we don't have any such option to automatically restart the identity values.

Example : Specifying the CYCLE option of the Sequence object, so the sequence will restart automatically when the max value is exceeded

CREATE SEQUENCE [dbo].[SequenceObject]

START WITH 1

INCREMENT BY 1

MINVALUE 1

MAXVALUE 5

CYCLE