Ben Richardson

Difference between Identity & Sequence in SQL Server

August 15, 2018 by

In SQL Server, both the SEQUENCE object and IDENTITY property are used to generate a sequence of numeric values in an ascending order. However, there are several differences between the IDENTITY property and SEQUENCE object. In this article, we will look at these differences.

Difference 1:

The IDENTITY property is tied to a particular table and cannot be shared among multiple tables since it is a table column property.

On the flip side the SEQUENCE object is defined by the user and can be shared by multiple tables since is it is not tied to any table.

Let’s understand this difference with the help of a simple example.

Execute the following script to create a database and some tables.

In the script above, we create the ShowRoom database with three tables Cars1, Cars2 and Cars3. The table Cars1 has an id column which has an IDENTITY property that starts with a 1 and increments by 1. You can see here that the IDENTITY property is tied to a particular column of a table. This IDENTITY property cannot be shared with other tables for instance the tables Cars2 and Cars3. The other two tables Cars2 and Cars3 do not have any IDENTITY property.

Now let’s create a SEQUENCE object, execute the following script:

In the script above we create a SEQUENCE object namely SequenceCounter. Now let’s share this sequence object between the Cars2 and Cars3 tables.

We will insert some rows into both the tables using the SEQUENCE object we just created. You will see that the SEQUENCE object will be shared between the two tables.

Let’s first add three records in the Cars2 table:

You can see that for id column of the Cars2 table we are using a NEXT VALUE FOR clause which will return the next value in the SEQUENCE starting with 1.

Let’s add three records to Cars3 tables using the same SEQUENCE object. Execute the following script:

Now let’s see what is being inserted for the Id column of the Cars2 and Cars3 tables. Execute the following script:

The output looks like this:

You can see that the values for the id column for the Cars1 table are 1, 2, 3 and for Cars3 table the values are 4, 5, 6. Since we are using SEQUENCE object to insert values for the id column and since SEQUENCE object is shared among the tables, therefore the values for id column in Cars3 table are basically continuation of the values in id column of the Cars2 table.

Difference 2:

To generate the next IDENTITY value, a new row has to be inserted into the table. On the other hand, the next VALUE for a SEQUENCE object can simply be generated using the NEXT VALUE FOR clause with the sequence object.

Let’s see this difference in action.

In the ShowRoom database we have a table Cars1 with an IDENTITY property on the id column. To get the next value for the IDENTITY, we have to insert a new row in the Car1 table. Take a look at the following script:

The output looks like this:

You can see that the id column has value of 1. There is no other way to increment the value for the IDENTITY property tied to the id column of Cars1 table, except by inserting a new row in the Cars1 table.

On the other hand, the value for a SEQUENCE object can be incremented without inserting a row into a table. Execute the following script:

The output looks like this:

You can see that previously, the value for the SequenceCounter SEQUENCE object was 6, now it has been incremented to 7 without inserting a new row to any table.

Difference 3

The value for the IDENTITY property cannot be reset to its initial value. In contrast, the value for the SEQUENCE object can be reset.

Take a look at the following script to see how a value can be reset using SEQUENCE object.

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. For instance in the above script, the SEQUENCE value is reset 1 once the maximum value i.e. 3 is reached. Therefore, if you execute the following script four times, you will see that 1 will be returned

Difference 4

A maximum value cannot be set for the IDENTITY property. On the other hand, the maximum value for a SEQUENCE object can be defined.

The maximum value that the IDENTITY can take is equal to the maximum value of the data type of the column that the IDENTITY property is tied to. For example, the IDENTITY property of the id column of the Cars1 table can take the maximum value that the INT data type can hold since the type of the id column is INT.

For a SEQUENCE object the MAXVALUE clause can be used to set the maximum value as shown in the following example.

In the above script, a SEQUENCE object has been created with a maximum value of 3. If you increment the value of this SEQUENCE beyond 3, following error will be thrown:


See more

For a new SQL Server database design tool with SSMS and Visual Studio IDE integration as well as integrated source control, check out ApexSQL Model.





Ben Richardson

Ben Richardson

Ben Richardson runs Acuity Training a leading provider of SQL training the UK. It offers a full range of SQL training from introductory courses through to advanced administration and data warehouse training – see here for more details. Acuity has offices in London and Guildford, Surrey. He also blogs occasionally on Acuity’s blog

View all posts by Ben Richardson
Ben Richardson
778 Views