Click here to Skip to main content
15,298,916 members
Articles / Programming Languages / SQL
Technical Blog
Posted 3 Dec 2017

Tagged as

Stats

4K views
1 bookmarked

Behaviour of IDENTITY Columns and SEQUENCES with TRANSACTIONS

Rate me:
Please Sign up or sign in to vote.
5.00/5 (1 vote)
3 Dec 2017CPOL2 min read
Behavior 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.

SQL
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.
SQL
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:

image

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

image

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.

SQL
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.

SQL
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:

SQL
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:

image

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Manjuke Fernando
Technical Lead Air Liquide Industrial Services (Singapore)
Singapore Singapore
My passion lies in building business intelligence and data-based solutions, writing about things I work with and talking about it. New technologies relevant to my line of work interest me and I am often seen playing with early releases of such technologies.

My current role involves architecting and building a variety of data solutions, providing database maintenance and administration support, building the organization’s data practice, and training and mentoring peers.

My aspiration over the next several years is to achieve higher competency and recognition in the field of Data Analytics and move into a career of data science.


Specialities: SQL Server, T-SQL Development, SQL Server Administration, SSRS, SSIS, C#, ASP.Net, Crystal Reports

Comments and Discussions

 
SuggestionSolution can be Set Identity ON/OFF or RESEED Pin
Aqeeel20-Dec-17 23:49
MemberAqeeel20-Dec-17 23:49 
GeneralRe: Solution can be Set Identity ON/OFF or RESEED Pin
Manjuke Fernando22-Jan-18 22:06
professionalManjuke Fernando22-Jan-18 22:06 
True. But there are some business logics where they generate the the identity and use it in the subsequent processes. If this procedure is being called from multiple clients, then the reseed option might give some unexpected result.
QuestionWhy value increase after rollback when you use identity column or sequence Pin
Mou_kol3-Dec-17 21:46
MemberMou_kol3-Dec-17 21:46 
AnswerRe: Why value increase after rollback when you use identity column or sequence Pin
Victor Nijegorodov9-Dec-17 4:16
MemberVictor Nijegorodov9-Dec-17 4:16 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.