SQL Server 2019 Behavior change with scope of SET IDENTITY_INSERT ON/OFF

Question

We are migrating to SQL Server 2019 RTM version and noticed that one of our stored procedures that uses SET IDENTITY_INSERT ON/OFF statements are failing which works properly in SQL Server 2017 and earlier.

Even changing the compatibility level to SQL Server 2017 does not work the same way in SQL Server 2019. This looks like a bug/behavior change that is not documented anywhere.

Anyone encountering similar issues or know if it is a reported problem? I tried searching for it but another person who reported the issue was a combination of ODBC and SQL Server 2019 CTP versions.

We have lots of customers running earlier versions of our product on SQL Server 2017 and earlier with this stored procedure that we cannot afford to change but still want to migrate to SQL Server 2019 and now this has become a show stopper for our SQL Server 2019 migrations.

Here is code snippet that fails in SQL Server 2019 but works fine in SQL Server 2017 and before

How to reproduce the behavior.

CREATE PROC proc_inner
AS
BEGIN
    SET IDENTITY_INSERT [#TMP_MESSAGE] ON;

    INSERT INTO [#TMP_MESSAGE] (DCORP, ENTITYKEY, SEQNO, MESSAGE)
        SELECT 
            'test', 1, 1, 'bdkfsjk';

    SET IDENTITY_INSERT #TMP_MESSAGE OFF;
END;
GO

CREATE PROC proc_outer
AS
BEGIN
    IF OBJECT_ID('TEMPDB..[#TMP_MESSAGE]') IS NULL
    BEGIN
        CREATE TABLE [#TMP_MESSAGE]
        (
             DCORP     CHAR(10), 
             ENTITYKEY INT, 
             SEQNO     INT IDENTITY(1, 1), 
             MESSAGE   VARCHAR(8000)
        );
    END;

    EXEC proc_inner;

    SELECT *
    FROM #TMP_MESSAGE;
END;
GO

EXEC proc_outer; -- this statement fails on SQL 2019

In SQL Server 2019 we get this error:

Msg 544, Level 16, State 1, Procedure proc_inner, Line 5 [Batch Start Line 36]
Cannot insert explicit value for identity column in table '#TMP_MESSAGE' when IDENTITY_INSERT is set to OFF. (0 rows affected)

In SQL Server 2017 and earlier, the same statement works properly.

Answer 1

I received a notification from MS support that it is in fact a bug with the new functionality so in case if anyone has issues with it the temporary workaround is to disable the feature using two trace flags:

====

In the meantime if anyone has upgraded to SQL 2019 (or for any plans) the workarounds are

Is to disable the new feature (reduced compilations for temporary tables) by enabling the trace flags Even if we move to regular tables (non-temp table) we wont face this issue (but as this is will be a product level change- I don’t think it will be feasible at this point of time); So the feasible workaround until we fix this issue :

Enabling the trace flag 11036;11048 at the SQL Startup Parameters ;