asked
Why does the identity value start with 2 in 2008 R2 and with 1 in 2012?
I have the same SSIS package and a target table with the same definition one on SQL Server 2008 R2 and one on SQL Server 2012. The SSIS package was originally built on 2008 R2 and scheduled to run on 2008 R2 SQL agent. The target is also 2008 R2. The same SSIS package has been upgraded using the upgrade wizard in 2012 and scheduled to run on 2012 SQL agent as well. The table structure on both versions are same. The identity column is set as 1,1 on both versions. However, in 2008 R2 it starts this column value starts with 2 and in 2012 it starts with 1.Can someone please solve this weird puzzle for me. I didnt create this but I am on maintenance of these jobs so I am really not sure What's happening. Please help.
This site works by you voting. For all helpful answers, please indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark.
Maybe there was allready one row in the table of 2012? Make a Test: reseed to 0 and try again
Is this table recreated each time or do you just truncate/delete the contents? If it's recreated I would start with just some SQL testing to make sure the SQL engine works the way you would expect: create table test (ID int identity(1,1) not null); insert test default values; select * from test; drop table test; I just tried this on SQL 2008 R2, 2012 and 2014. In all cases I got the value 1 in the identity column. I can't think why it wouldn't work the same for a new table in any version of SQL Server. If you truncate the table the identity should be re-seeded to 1 (ie. 1 will be the next value output). If you delete the rows, the identity value is not reset and the next insert will get the next value in the sequence. Another possibility is perhaps the job inserts row 1 and then rolls back later. The identity value is not reset if a transaction fails or is rolled back.
17 People are following this question.
