The culprit behind a TRANSACTION statement in SQL

A small Beginning: 

One of my friend works as a Staging Analyst in an MNC. He was assigned to do some manual INSERTs into a particular table with a valid logic. Like us, he even did put his INSERT logic inside a single TRANSACTION so that if something goes wrong then Transaction should ROLLBACK. No doubt that’s a good practice for all developers. He executed his INSERT script and unfortunately the TRANSACTION rolled back due to some error. He was likely happy cause he had used the code inside a single TRANSACTION. After fixing the error, he had executed again the INSERT script successfully and then went to check the inserted data in TARGET table. He was surprised seeing the discontinuous value in the identity column. He immediately called me for help. Finally I got something to help him certainly after understanding his whole scenario.

My whole testing and resolution for the issue goes here. I have made this article so realistic to make a feel of it!

Objective:

How we should handle a misfire of rollback transaction during any DML execution? A successful TRANSACTION does not harm anything but an unsuccessful one may harm to your IDENTITY column of a table. We will learn here how to overcome the situation, if a rolled back transaction affects an IDENTITY value? Delete only dissociated IDENTITY records from a table(if inserted accidentally) and then reseeding to its original value without affecting any earlier\contiguous records.

Steps to the solution: 

Note: EmpID is an Identity column and has increment of +1 from starting of seed value 1.

--Table creation
CREATE TABLE [dbo].[T_Emp](
	[empid] [int] IDENTITY(1,1) NOT NULL,
	[LastName] [varchar](20) NULL,
	[FirstName] [varchar](20) NULL,
	[Location] [varchar](20) NULL,
	[ManagerID] [int] NULL,
	[obsoleted] [smallint] NULL
) ON [PRIMARY]

--Dummy data inserts
Insert Employee
	Values	('Sahoo', 'Prasad', 'Ahmedabad', 0, 0),
			('Choudhury', 'Pravas', 'Bangalore',1, 0),
			('Rout', 'Priti','Bangalore', 1, 1 ),
			('Dave', 'Ankit', 'Delhi', 1, 0),
			('Bhatt', 'Kandarp', 'Pune', 1, 0),
			('Upadhyay', 'Rushi', 'Chennai', 1, 0)

(Pic 1.1)

2. INSERT statement with a TRANSACTION control.

Suppose your HR asked you to add 3 new employee details in the Employee table. Of-course you will take those employee details except EmpId from HR department and then you will insert those manually.

Play a Game: Put the insert statement in a TRANSACTION and then ROLLBACK immediately without committing it.

--Insert 3 new Emp records as per HR's request
Begin transaction
Insert Employee
	Values	('Mehta', 'Hilor', 'Mumbai', 1, 0),
			('Tangella', 'Phani', 'Hydrabad',1, 0),
			('Puhan', 'Prabhas','Orissa', 1, 1 )
Rollback transaction;
--Forcefully rolled back the transaction

The above code looks like, no data are going to be inserted in the Employee table as it has a ROLLBACK statement at the end. Lets have a look on the execution(Pic 2.1). (I have used ROW CONSTRUCTOR method to insert the data)

(Pic 2.1)

Looks like, 3 rows are being affected by the TRANSACTION. Alright now it's time to confirm that nothing have been inserted on execution to our previous code. Lets do a 'select *' on Employee table

(Pic 2.2)

The above screen shot looks like, we are OK as of now.

Lets insert those 3 employee details given by HR dept in the Employee table and COMMIT the TRANSACTION. I have used OUTPUT clause to see the data just been inserted.

--Committed Transaction
Begin transaction
Insert Employee
output inserted.*
	Values	('Mehta', 'Hilor', 'Mumbai', 1, 0),
			('Tangella', 'Phani', 'Hydrabad',1, 0),
			('Puhan', 'Prabhas','Orissa', 1, 1 )
commit transaction;

(Pic 2.3)

(Pic 2.4)

Oh my God! what happened to the EmpId column? Not fair.. I have not inserted any data so far but why my identity column seeded?

--Logic to see the dissociated records before deleting those
select * from Employee
where empid not in 
(select distinct es.empid from Employee as ES
inner join (select *, ROW_NUMBER() over(order by empid) as NewIdentity from Employee) as ET
on es.empid = et.NewIdentity where es.empid = ET.empid
)

--Manually a record have inserted with a force flag to identity
set identity_insert employee on
insert employee (empid, LastName, FirstName, Location, ManagerID, obsoleted)
values (7,'Mehta', 'Hilor', 'Mumbai', 1, 0)
set identity_insert employee off
--IDENTITY value will be reseeded here.
declare @maxid int
set @maxid = (select max(EmpID) from Employee)
dbcc checkident('Employee', reseed, @maxid);