Why doesn't SQL Server rollback the identity_insert state?

Question

I have a large script for setting up a new database that does lots of identity inserts on many tables; while I'm working on extending it I quite often have transient errors due to it being a work in progress. If one of these errors is between set identity_insert [table] on and set identity_insert [table] off the script refuses to run a second time with the following error:

Msg 8107, Level 16, State 1, Line 15
IDENTITY_INSERT is already ON for table 'x'. Cannot perform SET operation for table 'y'.

This is a pain! To work around it I highlight the 'off' line and run that manually, or kill the connection and reconnect (identity insert is per session I gather).

I have the whole script wrapped in a transaction, with set xact_abort on at the top so my expectation would be for the server to return to the pre-error state.

Why is this not the case? Am I doing anything wrong, and are there any more robust approaches than what I have here?

The following is a minimal test-case to reproduce the behavior. Run it twice and notice the error changes from a type conversion error in the first run to the identity_insert error on subsequent runs:

-- setup test tables for convenience
if not exists (select * from sys.tables where name = 'bill')
begin
 create table bill (id int identity primary key);
end
if not exists (select * from sys.tables where name = 'ben')
begin
 create table ben (id int identity primary key, height int);
end
set xact_abort on -- automatically rollback transaction on error
begin tran
 print 'adding bill records'
 set identity_insert bill on; -- breaks unexpectedly on *second* run
 insert into bill (id) values (1);
 set identity_insert bill off;
 print 'adding ben records'
 set identity_insert ben on;
 select * from ben
 -- insert that intentionally causes error due to type conversion failure
 insert into ben (id, height) values (1, 'not an int');
 set identity_insert ben off; -- never gets run, as expected
commit

Output from first run (expected behaviour):

adding bill records
adding ben records
Msg 245, Level 16, State 1, Line 22
Conversion failed when converting the varchar value 'not an int' to data type int.

Output from second run (unexpected behaviour):

adding bill records
Msg 8107, Level 16, State 1, Line 14
IDENTITY_INSERT is already ON for table 'data-test.dbo.ben'. Cannot perform SET operation for table 'bill'.

SELECT @@version gives:

Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)

I've tried this both in management studio and in visual studio just to check it's not an odd client bug.

Answer 1

This is a session setting; there is nothing to roll back. If you had issued SET DATEFORMAT DMY; or SET LANGUAGE FRENCH;, would you expect an error to revert you to MDY or english_us? What if you had SET SHOWPLAN on, would you expect a rollback to stop showing you plans on subsequent queries?

To be fair, the IDENTITY_INSERT case is a little special, since it's the only session setting I can think of off the top of my head that interacts directly with a table. But it's still applicable only to your session. The thinking is probably that you could go on to insert many rows in multiple batches, even after an error has been raised (as long as it hasn't severed the connection).

As an aside, you're using SQL Server 2012, so why are you using SQL Server 7.0 error handling techniques? You could use TRY/CATCH instead of SET XACT_ABORT and then turn the setting off in the CATCH if it's on (which you can check). For a good primer on error handling, see Erland Sommarskog's articles on Error and Transaction Handling in SQL Server:

• Part One– Jumpstart Error Handling
• Part Two - Commands and Mechanisms
• Part Three - Implementation

• Tim Abell: For the record, I've looked into the try-catch system, and while it would work it's massive overkill for something that will only happen during development of this one-off data migration script. Additionally I'd have to parse the error to work out which of the many tables has identity_insert still on in order to turn the right one off again. I looked into checking at the top of the script but that's also a non-starter (ref stackoverflow.com/q/10637976/10245 ). Overall thanks for the answer but I think my current approach is actually still optimal, albeit annoying. I feel more informed.
• Tim Abell: Oh by the way the second article you linked to encourages use of set xact_abort on in addition to the try-catch system, so I was wondering why you suggest not using it? I find it useful to guarantee rollback of a half done multi-table import.
• Aaron Bertrand: In addition to, maybe (though I don't use it personally). Not instead of. Remember too that that was just an aside - it wasn't the main thrust of my answer, which was answering your question about why this rollback doesn't occur magically and that it is not a bug, not suggesting cumbersome workarounds. Using try/catch on just the SET statements, of course, would not be all that crazy I suspect...
• Tim Abell: Ok cool, that makes sense, thanks. The main thrust of your answer was very useful as it's changed the way I think about it.