Hi All,

I would like to share a simple interview question. This is very simple yet many developers overlooks or not taking care of it.

Do you know what happens to identity value if transaction fails? Do you think that identity value of table is unchanged after failure?

Before looking at the solution below, try to memorize ACID properties of transaction and answer it.

Check-out the POC shown below & wobble your mind 🙂


-- Create Test Table
Create Table dbo.DemoTable
(id int identity(1,1) Primary Key Clustered,
CountryName varchar(100))
Go

-- Check what's inside it
Select * From dbo.DemoTable
Go

-- First shot
Begin Tran
Begin Try
Insert Into dbo.DemoTable Values ('Singapore')
Commit Tran
End Try
Begin Catch
If @@TranCount > 0
Rollback Tran
End Catch
Go

-- Check what's inside it
Select * From dbo.DemoTable
Go

-- Second shot
Begin Tran
Begin Try
Insert Into dbo.DemoTable Values ('India')

-- Intentional Error
Select 1/0

Commit Tran
End Try
Begin Catch
If @@TranCount > 0
Rollback Tran
End Catch
Go

-- Check what's inside it
Select * From dbo.DemoTable
Go

-- Third shot
Begin Tran
Begin Try
Insert Into dbo.DemoTable Values ('United States')
Commit Tran
End Try
Begin Catch
If @@TranCount > 0
Rollback Tran
End Catch
Go

-- Check what's inside it
Select * From dbo.DemoTable
Go

-- Drop the table
Drop Table dbo.DemoTable
Go

Amazed ?? You are welcome.
Thanks, Khilit