Recently, someone on my team had an issue with automated database tests failing and we discovered a change Microsoft made to the SQL engine. If you use the DBCC CHECKIDENT command to RESEED a table it could impact you as well.
Our database test was succeeding when run using a local visual studio 2013/2015 in memory database instance, but was failing when trying to run them on an actual 2008R2 SQL Server instance. You can see the difference below.
Running on SQL 2008 R2
This is the expected behavior where the next identity after a reseed will be the reseed value + 1.
However, in SQL 2012 Microsoft changed the functionality of the command such that it no longer gave you the reseed value + 1, but instead just uses the reseed value in this situation.
Running the above query on SQL 2012 generates the following results:
There is a Microsoft Connect item that outlined the issue and was closed as By Design.
Apparently the documentation team did not get the memo on the change by design because MSDN still shows that you should get the reseed value + 1.
Since our tests normally run in the local in-memory visual studio (2013/2015) instance using the SQL 2012/2014 engine we get different Checksum values returned than when the test is ran on a physical SQL 2008 R2 instance.
This is good to be aware of if you are using DBCC CheckIdent to Reseed a table. Your results will vary based on the version of SQL you are running against.
Have you found any interesting changes when upgrading to SQL 2012?
Let us know in the comments below.
