Identity seed increased when using IDENTITY_INSERT

Question

Running this snippet on SQL Server 2008 produces different result than on SQL Server 2014. I need them to produce the same result.

My scenario is that I am writing unit tests (using tsqlt) and this code is part of a huge procedure which I am trying to test. In my test code I can do anything because that code will never end up in production. This "bug" does no damage in production, but is giving me problems when writing my tests. Meaning, I do not want to change the code, only the tests.

My local dev environment is SQL Server 2014 but some of our CI environments are still SQL Server 2008.

• My first question is if there is any way to avoid this (bug) on 2008?
• My second question is if there is any way to make 2014 behave the same as 2008 and thus making the tests pass on both 2008 and 2014. Any setting or trace flag or hack?

My current hack is to reseed the table to 0 (instead of 1) in case server is 2008. I do no not like this as I will need to change it or remove it when our environments are upgraded.

IF CHARINDEX('2008', @@VERSION) > 0 BEGIN
        -- HACK to fix identity bug in SQL 2008.
        DBCC CHECKIDENT('...peter', RESEED, 0);
END

Troublesome code:

IF OBJECT_ID('tempdb..#peter') IS NOT NULL DROP TABLE #peter;
CREATE TABLE #peter(ID INT IDENTITY(1, 1), VALUE CHAR(10));

SET IDENTITY_INSERT #peter ON;
INSERT INTO #peter( ID, VALUE )
VALUES  ( -1,'Thing' ); -- Explicit negative ID value inserted here
SET IDENTITY_INSERT #peter Off;

INSERT INTO #peter( VALUE )
VALUES  ( 'Stuff' );

SELECT * FROM #peter;

Result in SQL Server 2014:

SQL Server 2008:

Server versions:

Microsoft SQL Server 2008 (SP4)  
Microsoft SQL Server 2014 

Answer 1

This is a known behavior change that was reported on Connect and closed as "by design." You'll notice that the docs for 2008 & 2014 are slightly different, partially to reflect that the output of commands like DBCC CHECKIDENT() (2008 vs. 2014) better reflect reality.

The workaround you're using now is as good as any I can think of, since there is no magic trace flag to change the behavior of either version. It does seem odd to me that you prioritize your tests over the code. You should be making your tests so that they can interpret all code paths, not changing the code to match the one output the test expects. I have to wonder why the test even cares what surrogate ID value is generated by the database, because you shouldn't IMHO. These should be invisible to users and only used for referential integrity.