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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
CREATE Database ShowRoom; GO USE ShowRoom; CREATE TABLE Cars1 ( id INT PRIMARY KEY IDENTITY(1,1), name VARCHAR(50) NOT NULL, company VARCHAR(50) NOT NULL, power INT NOT NULL ) CREATE TABLE Cars2 ( id INT, name VARCHAR(50) NOT NULL, company VARCHAR(50) NOT NULL, power INT NOT NULL ) CREATE TABLE Cars3 ( id INT, name VARCHAR(50) NOT NULL, company VARCHAR(50) NOT NULL, power INT NOT NULL ) |
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:
|
1 2 3 4 |
CREATE SEQUENCE [dbo].[SequenceCounter] AS INT START WITH 1 INCREMENT BY 1 |
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:
|
1 2 3 |
INSERT INTO Cars2 VALUES (NEXT VALUE FOR [dbo].[SequenceCounter], '208', 'Peugeot', 5400) INSERT INTO Cars2 VALUES (NEXT VALUE FOR [dbo].[SequenceCounter], 'C500', 'BMW', 8000) INSERT INTO Cars2 VALUES (NEXT VALUE FOR [dbo].[SequenceCounter], 'C500', 'Peugeot', 5400) |
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:
|
1 2 3 |
INSERT INTO Cars3 VALUES (NEXT VALUE FOR [dbo].[SequenceCounter], 'C500', 'Mercedez', 5000) INSERT INTO Cars3 VALUES (NEXT VALUE FOR [dbo].[SequenceCounter], 'Prius', 'Toyota', 3200) INSERT INTO Cars3 VALUES (NEXT VALUE FOR [dbo].[SequenceCounter], 'Civic', 'Honda', 1800) |
Now let’s see what is being inserted for the Id column of the Cars2 and Cars3 tables. Execute the following script:
|
1 2 |
SELECT * FROM Cars2 SELECT * FROM Cars3 |
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:
|
1 |
INSERT INTO Cars1 VALUES ('Corrolla', 'Toyota', 1800) |
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:
|
1 |
SELECT NEXT VALUE FOR [dbo].[SequenceCounter] |
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.
|
1 2 3 4 5 6 7 |
CREATE SEQUENCE [dbo].[RecycleSequence] AS INT START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 3 CYCLE |
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
|
1 |
SELECT NEXT VALUE FOR [dbo].[RecycleSequence] |
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.
|
1 2 3 4 5 |
CREATE SEQUENCE [dbo].[MaxSequence] AS INT START WITH 1 INCREMENT BY 1 MAXVALUE 3 |
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:
Other great articles from Ben
| Sequence Objects in SQL Server |
| Understanding the GUID data type in SQL Server |
| Difference between Identity & Sequence in SQL Server |
View all posts by Ben Richardson
- Working with the SQL MIN function in SQL Server - May 12, 2022
- SQL percentage calculation examples in SQL Server - January 19, 2022
- Working with Power BI report themes - February 25, 2021