Behaviour of IDENTITY Columns and SEQUENCES with TRANSACTIONS

Few days ago, I was caught in a discussion with a couple of my colleagues, regarding a problem they are facing with an IDENTITY column.

The issue was that when a transaction is rolled back, the identity seed isn’t rolling back as expected. This was causing the business application to lose the id sequence.

There is no fix or a workaround for this. All that I could provide was an explanation.

I will illustrate the issue and an explanation why it’s happening.

Behaviour of IDENTITY Columns

We will create the following table to hold employee details.

CREATE TABLE dbo.EmployeeInfo(
	Id			INT IDENTITY(1,1) NOT NULL,
	EmpName		VARCHAR(100) NOT NULL
)

Now we will insert few records to the table in the following manner:

1. Without a transaction
2. With a transaction. But we will rollback the transaction.
3. With a transaction. But we will commit it.

INSERT INTO dbo.EmployeeInfo (EmpName)
VALUES('John')

BEGIN TRAN
	INSERT INTO dbo.EmployeeInfo (EmpName)
	VALUES('Jane')
ROLLBACK

INSERT INTO dbo.EmployeeInfo (EmpName)
VALUES('James')

SELECT 
	EI.Id
	,EI.EmpName 
FROM
	dbo.EmployeeInfo AS EI

And when checked, you could see the following results:

Usually, the expectation is to see the employee “James” with an Id of 2.

What you should understand here is that this isn’t a flaw or a bug. This is the exact intended behaviour and it has been explained in the following MSDN article:

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property

Behaviour of SEQUENCES

SEQUENCEs were introduced in SQL Server 2012. The purpose of the SEQUENCE objects was to aid in handling the auto increment numbers, in case you prefer to handle the sequence without using an IDENTITY column.

First, we will create a sequence object. The minimum syntax required to create a sequence object is a name and the data type. Additionally, you can mention many other attributes like starting index, increment seed, etc.

CREATE SEQUENCE dbo.TempNumberSequence AS INT

Further details regarding other options can be found at the following URL:

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql

Now we will create a similar table like we created in the previous example, but without an IDENTITY column.

CREATE TABLE dbo.EmployeeInfoSeq(
	Id			INT 
	,EmpName	VARCHAR(100) NOT NULL
)

We will insert 3 records in the same way like we did in the previous example:

DECLARE @NextSeq AS INT
SELECT @NextSeq = NEXT VALUE FOR dbo.TempNumberSequence
INSERT INTO dbo.EmployeeInfoSeq (
	Id
	,EmpName
)
VALUES (
	@NextSeq
	,'John'
)
GO

DECLARE @NextSeq AS INT
SELECT @NextSeq = NEXT VALUE FOR dbo.TempNumberSequence
BEGIN TRAN
	INSERT INTO dbo.EmployeeInfoSeq (
		Id
		,EmpName
	)
	VALUES (
		@NextSeq
		,'Jane'
	)
ROLLBACK
GO

DECLARE @NextSeq AS INT
SELECT @NextSeq = NEXT VALUE FOR dbo.TempNumberSequence
INSERT INTO dbo.EmployeeInfoSeq (
	Id
	,EmpName
)
VALUES (
	@NextSeq
	,'James'
)
GO

Afterwards if you check, you will see the following results:

Hope this will help you in your day to day development work.