Identity behavior in sql server 2008 and 2012
-
Hi - We migrated from sql server 2008 to 2012. We have a stored proc which inserts dates into a table. We insert an initial value wherein the identity key value is -1, with an insert statement. For the rest of the dates, we have a while loop. within the while loop, the next identity key value is resulting as 0 in sql server 2012 and as a 1 in sql server 2008. Both server have the table's field name defined with identity_seed as 0, and increment as 1. Why does sql server 2008 have 1 as the next value after -1, and sql server 2012 have 0 as the next value after -1? Thank you.
-
Hi - We migrated from sql server 2008 to 2012. We have a stored proc which inserts dates into a table. We insert an initial value wherein the identity key value is -1, with an insert statement. For the rest of the dates, we have a while loop. within the while loop, the next identity key value is resulting as 0 in sql server 2012 and as a 1 in sql server 2008. Both server have the table's field name defined with identity_seed as 0, and increment as 1. Why does sql server 2008 have 1 as the next value after -1, and sql server 2012 have 0 as the next value after -1? Thank you.
The fact you have a while-loop is filling me horror. However, without being able to see the SP code we can't possibly comment on what might be going wrong. You have contradicted yourself however, you stated
Quote:
We insert an initial value wherein the identity key value is -1,
but you state
Quote:
Both server have the table's field name defined with identity_seed as 0, and increment as 1.
Without checking I can't confirm, but perhaps there was a hole in 2008 that allowed you to trample all over the IDENTITY column. You should use
IDENTITY (-1,1)if you want the first key to be -1 (but I suspect you don't). Try posting the code so we can suggest ways of improving it ... like getting rid of the WHILE loop :laugh: -
Hi - We migrated from sql server 2008 to 2012. We have a stored proc which inserts dates into a table. We insert an initial value wherein the identity key value is -1, with an insert statement. For the rest of the dates, we have a while loop. within the while loop, the next identity key value is resulting as 0 in sql server 2012 and as a 1 in sql server 2008. Both server have the table's field name defined with identity_seed as 0, and increment as 1. Why does sql server 2008 have 1 as the next value after -1, and sql server 2012 have 0 as the next value after -1? Thank you.
Sounds like you're
RESEEDing the table:DBCC CHECKIDENT (Transact-SQL) | Microsoft Docs[^]:
If rows are present in the table, the next row is inserted with the
new_reseed_valuevalue. In version SQL Server 2008 R2 and earlier, the next row inserted usesnew_reseed_value+ the current increment value.So if you reseed to
0, and your increment is1, the next value in SQL 2008 R2 or earlier will be1, whereas the next value in SQL 2012 or later will be0.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
"These people looked deep within my soul and assigned me a number based on the order in which I joined" - Homer
-
Sounds like you're
RESEEDing the table:DBCC CHECKIDENT (Transact-SQL) | Microsoft Docs[^]:
If rows are present in the table, the next row is inserted with the
new_reseed_valuevalue. In version SQL Server 2008 R2 and earlier, the next row inserted usesnew_reseed_value+ the current increment value.So if you reseed to
0, and your increment is1, the next value in SQL 2008 R2 or earlier will be1, whereas the next value in SQL 2012 or later will be0.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Sounds like you're
RESEEDing the table:DBCC CHECKIDENT (Transact-SQL) | Microsoft Docs[^]:
If rows are present in the table, the next row is inserted with the
new_reseed_valuevalue. In version SQL Server 2008 R2 and earlier, the next row inserted usesnew_reseed_value+ the current increment value.So if you reseed to
0, and your increment is1, the next value in SQL 2008 R2 or earlier will be1, whereas the next value in SQL 2012 or later will be0.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Hi - We migrated from sql server 2008 to 2012. We have a stored proc which inserts dates into a table. We insert an initial value wherein the identity key value is -1, with an insert statement. For the rest of the dates, we have a while loop. within the while loop, the next identity key value is resulting as 0 in sql server 2012 and as a 1 in sql server 2008. Both server have the table's field name defined with identity_seed as 0, and increment as 1. Why does sql server 2008 have 1 as the next value after -1, and sql server 2012 have 0 as the next value after -1? Thank you.
VK19 wrote:
Why does sql server 2008 have 1 as the next value after -1, and sql server 2012 have 0 as the next value after -1?
The answer was above, but I am curious why it matters. Certainly existing data would long be past zero or one. And if this is new data, always, then why does it matter?
-
VK19 wrote:
Why does sql server 2008 have 1 as the next value after -1, and sql server 2012 have 0 as the next value after -1?
The answer was above, but I am curious why it matters. Certainly existing data would long be past zero or one. And if this is new data, always, then why does it matter?
-
This is part of our datawarehouse project. We have some reports set up for data extract, and using some <> 0 conditions.
