What is Difference Between Sequence Object and Identity Column in SQL Server?. The Complete Microsoft SQL Server Developer Course 2023 [Videos].

In this Video, I am going to discuss the Difference Between Sequence Object and Identity Column in SQL Server with examples. I strongly recommended you read the Identity in SQL Server and Sequence in SQL Server Videos before proceeding to this Video where we discussed Identity and Sequence in SQL Server with examples. The difference between sequence and identity in SQL Server is one of the most frequently asked interview questions.

The sequence object is very much similar to the Identity property in SQL Server, in the sense that it generates a sequence of numeric values in an ascending order just like the identity property. However, there are several differences between them. So let start the discussion.

What is the Difference Between Sequence Objects and Identity Columns in SQL Server?

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

Example:

In the below example the Identity property tied to the Id column of the Employees table. 

Example: 

The sequence object is not tied to any specific table

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

Example of Sharing sequence object value with multiple tables in SQL Server:

Step 1: Create Customers and Users tables

Step 2: Please use the below SQL script to Insert two rows into the Customers table and three rows into the Users table. Please notice that the same sequence object is used to generating the ID values of both the Customers and Users tables.

SELECT * FROM Customers

SELECT * FROM Users

Notice the same sequence object has generated the values for ID columns (CustomerId and UserId) in both the tables 

In the case of Identity to generate the next identity value, a row has to be inserted into the table, whereas in the case of a sequence object we dont require adding a row into the table in order to generate the next sequence value. You can use the NEXT VALUE FOR clause to generate the next sequence value in SQL Server.

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

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

SELECT NEXT VALUE FOR [dbo].[SequenceObject]

The maximum value for the identity property cannot be specified. In the case of Identity, the maximum value will be the maximum value of the corresponding column data type on which the Identity property is specified. In the case of the sequence object, we 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 its data type.

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

CYCLE option

In the case of Sequence Object, you can use the CYCLE option to specify whether the sequence should restart automatically when the sequence object value reached the max or min value, whereas in the case of Identity property we dont have such an option to automatically restart the identity values.

Example: Let us specifying the CYCLE option of the Sequence object so that the sequence object value will restart automatically when the max value is reached.

So, in short, the difference between sequence and identity in SQL Server are as follows,

1. The Identity Property of a column is available from SQL Server 2000 whereas the Sequence object is available from SQL Server 2012. Identity cannot be controlled by application code whereas Sequence can be controlled by application code.
2. If any column is marked as Identity then we cannot insert data into this column directly. In that case, we must first turn off the Identity of the column whereas a Sequence object does not depend on the table so we can insert any value in the column.
3. We cannot get the value of an Identity column before inserting a record whereas we can get the value of the next Sequence number for a Sequence Object before inserting a record.
4. We can reseed an Identity property but we cannot change the step size whereas we can alter the Seed as well as the Step size of a Sequence object at any time.
5. The sequence is used to generate a database-wide sequential number whereas the identity column is tied to a table.