1

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:

2014

SQL Server 2008:

2008

Server versions:

Microsoft SQL Server 2008 (SP4)  
Microsoft SQL Server 2014 
4
  • It's normal for different versions of a database to have different behavior (though hopefully it's well-documented!), so having your unit tests aware of this is absolutely normal. You seem to have a preference against it, not a strong reason. Are you sure you need to solve this problem? – Jon of All Trades Apr 29 '15 at 14:44
  • Not a solution, but I'd rather make a decision about reseeding identity value with IDENT_CURRENT than based on version. – a1ex07 Apr 29 '15 at 14:45
  • @JonofAllTrades - Good point. I do not have any strong reason, It was my gut feeling telling me I was doing something bad. Having these different behaviors documented in the tests seems like a good idea. – Peter Henell Apr 29 '15 at 14:57
  • @a1ex07, Before calling the dbo.BigProcedure, the IDENT_CURRENT is the same in both environments. – Peter Henell Apr 29 '15 at 15:00
6

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.

3
  • Thank you for the link to connect, I was not able to find that information. I am not changing the code to match the test. I wanted to add "better" hacks in my test so that the output could be predicted. You are right, the Identity columns are not giving me any useful information. They should be removed from the tests completely. – Peter Henell Apr 29 '15 at 15:14
  • I am not changing the code to match the test. <-- sure you are, you add a check for version to perform a RESEED so that the test sees the same identity value regardless of version. That is the definition of changing the code to match the test. :-) – Aaron Bertrand Apr 29 '15 at 15:18
  • I was doing that in the setup of the test. Setting up my "known" state just as I mock tables and insert known data in order to be able to control the output. "Given the input of seed 0 on Sql2008 output should be this". – Peter Henell Apr 29 '15 at 21:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.