Different Between Identity And Sequence In SQL

Introduction

In this article, we are going to learn what is differences between IDENTITY and SEQUENCE.

First Difference.

IDENTITY property is only for a particular table it cannot use among multiple tables because it is a column property.

On another side, the SEQUENCE object is defined by the user so it can share among multiple tables.

Let us understand it by example.

USE [StudendDB];
CREATE TABLE Bike1
(
    id INT PRIMARY KEY IDENTITY(1,1),
    name VARCHAR(50) NOT NULL,
    colour VARCHAR(50) NOT NULL
)
CREATE TABLE Bike2
(
    id INT,
    name VARCHAR(50) NOT NULL,
    colour VARCHAR(50) NOT NULL
 )
 CREATE TABLE Bike3
(
    id INT,
    name VARCHAR(50) NOT NULL,
    colour VARCHAR(50) NOT NULL
)

In the above code, you can see that the bike1 table contains IDENTITY, and the bike2 and bike3 tables don’t contain IDENTITY.

Let’s create a SEQUENCE object.

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

The above script creates the SEQUENCE and we are going to share this SEQUENCE with bike2 and bike3.

Now let us insert 2 records in bike2 then bike3.

INSERT INTO Bike2 VALUES (NEXT VALUE FOR [dbo].[SequenceCounter], '208', 'Red')
INSERT INTO Bike2 VALUES (NEXT VALUE FOR [dbo].[SequenceCounter], 'C500', 'Blue')

INSERT INTO Bike3 VALUES (NEXT VALUE FOR [dbo].[SequenceCounter], '209', 'Black')
INSERT INTO Bike3 VALUES (NEXT VALUE FOR [dbo].[SequenceCounter], 'C600', 'White')

To see inserted value execute the following command.

SELECT * from Bike2
SELECT * from Bike3

Second Differences.

In IDENTITY to get the next column, you have to insert a new row in the table.

In SEQUENCE, we can generate the next value by using the NEXT VALUE FOR clause with the sequence object.

SELECT NEXT VALUE FOR [dbo].[SequenceCounter]

Third Differences.

The value for the IDENTITY property cannot be reset to its initial value but the value of the SEQUENCE object can be reset.

CREATE SEQUENCE [dbo].[ResetSequence]
 AS INT
 START WITH 1
 INCREMENT BY 1
 MINVALUE 1
 MAXVALUE 3
 CYCLE

To reset the value of a SEQUENCE object, you have to set the minimum and maximum values for the SEQUENCE and have to specify a CYCLE tag with the script.

Fourth Differences.

A maximum value cannot be set for the IDENTITY property but in the SEQUENCE object, we can set the maximum value.

CREATE SEQUENCE [dbo].[MaxSequence]
 AS INT
 START WITH 1
 INCREMENT BY 1
 MAXVALUE 5

In the above script, a SEQUENCE object has been created with a maximum value of 5. If you increment the value of this SEQUENCE beyond 5, then it will throw an error.

Also, Check How To Get All Column Names Of Table In SQL