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
Creating Sequence
Inserting the Records on Dev2 and Dev3.
Lets see All Records using “Select * From Dev2 ” , ” Select * from Dev3 ” .
Trying to insert the records in Dev1 with Identity Column.
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 .
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.
