question

bhojarajan_vtj avatar image
bhojarajan_vtj 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.
sql-server-2008-r2identity-column
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
west007 avatar image
west007 answered
Maybe there was allready one row in the table of 2012? Make a Test: reseed to 0 and try again
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David Wimbush avatar image
David Wimbush answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.