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
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:
Microsoft SQL Server 2008 (SP4) Microsoft SQL Server 2014