Difference between Identity & Sequence in SQL Server

1.The IDENTITY property cannot be shared among multiple tables because It’s a table column property.

  SEQUENCE Object is Created by user so it can be shared by multiple tables . It is not attached to any table.

 Creating Three Tables , Dev1 has Identity Column ID

Pic 1

Creating Sequence


Inserting the Records on Dev2 and Dev3.


Lets see All Records using “Select * From Dev2 ” , ” Select * from Dev3 ” .

pic 4

Trying to insert the records in Dev1 with Identity Column.

pic 5

Difference 2 . To generate the next IDENTITY value, a new row has to be inserted into the table.

 To Generate next VALUE for a SEQUENCE object use NEXT VALUE FOR clause with the sequence object.  SEQUENCE object can be incremented without inserting a row .

Next Sequence value 7

Let’s insert the record in Identity Column table . see the below picture.

Difference 3 . Sequence Object use Table Insert but Identity Column uses Clustered Index Insert.

Difference 4 . We can set Maximum value limit with Sequence not with Identity Column.

Above Four important difference between Identity Column and Sequence in Sql Server. i hope. it helpful .

Happy Coding Thanks.

Post Author: adama