↓
 
Home→T-SQL→IDENTITY_INSERT can be ON for only one table

IDENTITY_INSERT can be ON for only one table

The Intransigent Data Blog Posted on by

This article is a journey to understanding an exception raised when attempting to set IDENTITY_INSERT on in two nested stored procedures.  It hinges on the misinterpretation of an exception message, and the discussion veers into temporary-table-caching. The explanation, as always, is in SQL Server docs.

Temporary table caching

SQL Server caches the metadata of temporary tables that are created in stored procedures and other modules.  This reduces the overhead of creating and deleting temporary objects each time and is generally useful,  with certain considerations about table statistics, which are also persisted.

See Paul White’s articles for details 

Is IDENTITY_INSERT setting also cached?

As the articles explain, when caching is effective temporary tables are not disposed of when a procedure goes out of scope – nor even when explicitly dropped.  Rather they are truncated, renamed and retained for the next use.  

Statistics are preserved – and also are other table settings, such as the IDENTITY_INSERT setting.   If caching is unexpected by most users, this last effect is even more surprising.

This illustration uses two procedures, each of which creates a temporary table with the same structure and name.  Each sets IDENTITY_INSERT ON for the temporary table, then drops it.  dbo.OuterProcedure calls dbo.InnerProcedure.

CREATE OR ALTER PROCEDURE dbo.InnerProcedure
AS
    CREATE TABLE #TemporaryTableA (
        ID int IDENTITY,
        TextColumn varchar(50) NOT NULL
    )

    SET IDENTITY_INSERT #TemporaryTableA ON

    DROP TABLE #TemporaryTableA
GO

CREATE OR ALTER PROCEDURE dbo.OuterProcedure
AS
    
    CREATE TABLE #TemporaryTableA (
        ID int IDENTITY,
        TextColumn varchar(50) NOT NULL
    )

    SET IDENTITY_INSERT #TemporaryTableA ON

    DROP TABLE #TemporaryTableA

    EXEC dbo.InnerProcedure
GO

InnerProcedure can be executed multiple times without exception.  OuterProcedure, though, results in this exception:

Msg 8107, Level 16, State 1, Procedure dbo.InnerProcedure, Line 7 [Batch Start Line 38]
IDENTITY_INSERT is already ON for table 'tempdb.dbo.#AD71C20D'. Cannot perform SET operation for table '#TemporaryTableA'.

A first interpretation of this is that #TemporaryTableA, which is created in OuterProcedure, is cached (minus data) and reused when the same temporary table is created in InnerProcedure. The cached-object name is also visible in the first sentence of the error message.  The setting of IDENTITY_INSERT is preserved, which is why  requesting SET IDENTITY INSERT...ON for a second time fails.

RECOMPILE Prevents Caching

The referenced articles give conditions which prevent caching, one of which is calling the procedure WITH RECOMPILE.  Accordingly, calling the OuterProcedure thus executes without exception:

EXEC dbo.OuterProcedure WITH RECOMPILE 

This prevents caching of #TemporaryTableA in OuterProcedure so there is no preserved metadata for when InnerProcedure is called.

Is this really to do with temp table caching?

It looks like it is related, particularly since WITH RECOMPILE prevents the exception.  But it might be an artifact of nested procedure calls.

What if we change the name of the second (inner) temporary table and set IDENTITY_INSERT on that?  The same exception.  What if we change the schema of the table?  Same.  What if we try to set IDENTITY_INSERT on anther, non-temporary, table? Let’s try:

/* Revised inner procedure: different temp table name, schema, and IDENTITY_INSERT is set on another table! */
CREATE OR ALTER PROCEDURE dbo.InnerProcedure
AS
    CREATE TABLE #TemporaryTableX (
        ADifferentID int IDENTITY,
        NewTextColumn varchar(50) NOT NULL,
        AnotherColumn varchar(50) NULL
    )

    SET IDENTITY_INSERT dbo.SourceTableWithIdentity ON

    DROP TABLE #TemporaryTableX
GO

CREATE OR ALTER PROCEDURE dbo.OuterProcedure
AS
    
    CREATE TABLE #TemporaryTableb (
        IDf int IDENTITY,
        TextColumn varchar(50) NOT NULL
    )

    SET IDENTITY_INSERT #TemporaryTableb ON

    DROP TABLE #TemporaryTableb
    
    EXEC dbo.InnerProcedure
GO
EXEC dbo.OuterProcedure

Result

Msg 8107, Level 16, State 1, Procedure dbo.InnerProcedure, Line 8 [Batch Start Line 28]
IDENTITY_INSERT is already ON for table 'tempdb.dbo.#B32D56A1'. Cannot perform SET operation for table 'dbo.SourceTableWithIdentity'.

Note that the exception first mentions a cached temp table, then identifies the target non-temp table (SourceTableWithIdentity).

Finally, remove all reference to temporary objects altogether:

/* No reference to temporary tables */
CREATE OR ALTER PROCEDURE dbo.InnerProcedure
AS
    SET IDENTITY_INSERT dbo.SourceTableWithIdentity ON
GO

CREATE OR ALTER PROCEDURE dbo.OuterProcedure
AS
    SET IDENTITY_INSERT dbo.SourceTableWithIdentity2 ON
    
    EXEC dbo.InnerProcedure
GO
EXEC dbo.OuterProcedure 

/*
Msg 8107, Level 16, State 1, Procedure dbo.InnerProcedure, Line 3 [Batch Start Line 12]
IDENTITY_INSERT is already ON for table 'd2.dbo.SourceTableWithIdentity2'. Cannot perform SET operation for table 'dbo.SourceTableWithIdentity'.
*/

Same exception: again notice how the exception reveals that the instruction was to set IDENTITY_INSERT on dbo.SourceTableWithIdentity (the statement in InnerProcedure),  but that the operation was attempted on dbo.SourceTableWithIdentity2 – the table referenced in the calling OuterProcedure.

Thus this exception is not exclusively related to temp table caching. So what is the cause?

Only one table in scope can have IDENTITY_INSERT on

Now we know the exception is to do with IDENTITY_INSERT and not temporary table caching we can review the documentation and find the answer.

At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, SQL Server returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.   IDENTITY_INSERT documentation

As always with SQL Server, the exception message is perfectly precise.  Sometimes the human interpretation is not.

So is temporary table caching involved?

The original scenario came about because of the (wrong) assumption that dropping a temporary table would fully remove the table and all its related settings, including IDENTITY_INSERT.  That is not the case because of temp table caching.

Had the initial test used standard user tables then it would have succeeded because dropping a standard table does result in the table and all related settings being deleted.

Rewriting the original test with user tables succeeds because there are no table caching effects:

CREATE OR ALTER PROCEDURE dbo.InnerProcedure
AS
    CREATE TABLE TableA (
        ID int IDENTITY,
        TextColumn varchar(50) NOT NULL
    )
    SET IDENTITY_INSERT TableA ON
    DROP TABLE TableA
GO
CREATE OR ALTER PROCEDURE dbo.OuterProcedure
AS
    
    CREATE TABLE TableA (
        ID int IDENTITY,
        TextColumn varchar(50) NOT NULL
    )
    SET IDENTITY_INSERT TableA ON
    DROP TABLE TableA
    EXEC dbo.InnerProcedure
GO

EXEC dbo.OuterProcedure

/*
Commands completed successfully.
*/

So temporary table caching helped expose the single-IDENTITY_INSERT limit.

Lesson

Turn IDENTITY_INSERT off as soon as it’s no longer needed.

Comments are closed.

↑