Proof A Failed Insert Increases the Value of an Identity Column
Published: Oct 02, 2016
This is something I’ve seen cause plenty of confusion and so I thought I would investigate. It’s actually lucky that I did before decommissioning my old SQL 2008 R2 server as the same proof can’t be run on SQL Server 2014 (I’ve not tried SQL Server 2012).

Basically the proof is, as I had hoped, found in the transaction log. And this is where the problem occurs in SQL Server 2014 as the specific log records providing the proof don’t seem to exist. I’m not sure if it’s no longer being logged, just that they weren’t there when I looked for them.

Anyway, enough of that… using my SQL Server 2008 R2 machine, this is what I came up with…

Firstly we’ll create a table with an identity column:

if object_id('myTestTable') is not null drop table myTestTable
go

create table myTestTable
(
  
id int identity,
  
myValue varchar(10) not null
)


Next we want to insert a single row. This is simply so that when we look at the log in future we get a clean view because the first insert causes all manner of log records to be written which would cloud our analysis later on.

insert into myTestTable
select 'startTable'
go

dbcc checkident('mytestTable')
go


Okay, all is as expected. We have our table and we have 1 record, therefore we have a current identity value of 1.

So now let’s clear the log:

checkpoint
go


Once done we’ll insert a valid record into our table. This should increase our identity value to 2.

insert into myTestTable
select 'misc1'
go

select *
from fn_dbLog(null, null)
go


dbcc checkident('mytestTable')
go


Now, this is where things get interesting… did you spot the weird record in the transaction log? The LOP_IDENT_NEWVAL (which appears absent in SQL Server 2014) was NOT inside the same transaction as our insert (LOP_INSERT_ROWS).

Therefore you now have a hint as to what is happening… basically SQL Server is incrementing the identity and THEN dealing with the insert.

So what will happen when we have an insert that fails?

checkpoint
go

insert into myTestTable
select null
go

select *
from fn_dbLog(null, null)
go

dbcc checkident('mytestTable')
go


Exactly as suspected… the identity was incremented before the transaction even tried to begin and therefore we can see that despite the failure message the identity has already incremented.

This also performs in the same way if we rollback a transaction because SQL Server has already incremented the identity prior to the transaction even beginning therefore it cannot roll it back.

checkpoint
go

begin tran
insert into
myTestTable
select 'misc2'
rollback tran

select
*
from fn_dbLog(null, null)
go


So there you have it… proof that an identity will always increase despite failed inserts. Don’t ask me why it does it this way… I didn’t write it… but at least you now won’t be caught out by the behavior.

Lastly, we need to tidy up our test table, and we’re done.

if object_id('myTestTable') is not null drop table myTestTable
go
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron