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