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:
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.
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:
Once done we’ll insert a valid record into our table. This should increase our identity value to 2.
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?
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.
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.
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
So now let’s clear the log:
checkpoint
goOnce 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
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
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
Lastly, we need to tidy up our test table, and we’re done.
if object_id('myTestTable') is not null drop table myTestTable
go