Difference between SEQUENCE and IDENTITY in SQL Server

Both Identity and sequence used in SQL server to generate auto number. Apart from this there are multiple difference between them. Below are there

Feature	IDENTITY	SEQUENCE
Scope	Table level	Database level
Reusability	Cannot be reused	Can be shared by multiple tables
Syntax	Defined inside table	Sequence is a SQL server object so we It Created independently using CREATE SEQUENCE
Value Generation	Auto increment per insert	Generated manually using NEXT VALUE FOR
Reset / Restart	Hard & risky	Easy and supported
Caching	Yes	Yes(configurable)
Gaps	Possible	Possible
Min/Max / Cycle	Limited	Full control (MIN, MAX, CYCLE)
Thread Safety	Table restricted	Fully managed + better concurrency

Let’s see the demo

Identity we can use in a table.

Creating a table and insert few records.

create table emp_demo ( id int identity(1,1) not null primary key, nm varchar(50) ) insert into emp_demo(nm) values ('Bagesh'),('Amit')

See the records

Id is identity column and it generated automatically when we are insert the records in this table.

This identity column values we can not use in the different table. We cannot customize MIN / MAX / Cycle and also, we cannot manually fetch next number like sequence. If transaction fails, number is skipped.

SEQUENCE

Creating a sequence and see

CREATE SEQUENCE EmpSequence START WITH 1 INCREMENT BY 1;

Now creating table and using this sequence and inserting few records

CREATE TABLE Employee (     EmpID INT PRIMARY KEY DEFAULT NEXT VALUE FOR EmpSequence,     EmpName VARCHAR(50) );  INSERT INTO Employee (EmpName) VALUES ('Rohit'), ('Neha');

See the records in this table.

SELECT * FROM Employee;

This Sequence we can use in another table. See below.

CREATE TABLE Managers (     ManagerID INT DEFAULT NEXT VALUE FOR EmpSequence,     ManagerName VARCHAR(50) ); INSERT INTO Managers (ManagerName) VALUES ('Bagesh');

See the records now in this table

See Id here is 3 because 1 and 2 is consumed by table Employee. Now if we insert the records in the Employee table then it will be 4 let us see

SEQUENCE is Powerful & Flexible We can use it into multiple tables when we need numbering, we can custom min/max/cycle I means we can manually control the number.