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:
- Without a transaction
- With a transaction. But we will rollback the transaction.
- 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
SEQUENCE
s 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.