Options
Identity seed not preserved accross table rebuild.
nottinbe
Posts: 6
Assume a table Test with a column with IDENTITY(1, 1).
If I insert 3 rows into that table, the seed value as reported by DBCC CHECKIDENT will be 3.
If I then delete the row with PK = 3, DBCC CHECKIDENT will of course still be 3 - the PK 3 is not reused, and our system requires this assumption to hold true.
Next, I change something about Test that SQL Compare needs to do a table rebuild to accomplish. After the rebuild, DBCC CHECKIDENT will report the seed to be 2 - NOT 3.
The next time a row is inserted into Test the PK 3 is reused, and chaos follows.
How do you suggest I handle this problem? Is this something that you can\will fix in SQL Compare?
Thanks,
Brian
If I insert 3 rows into that table, the seed value as reported by DBCC CHECKIDENT will be 3.
If I then delete the row with PK = 3, DBCC CHECKIDENT will of course still be 3 - the PK 3 is not reused, and our system requires this assumption to hold true.
Next, I change something about Test that SQL Compare needs to do a table rebuild to accomplish. After the rebuild, DBCC CHECKIDENT will report the seed to be 2 - NOT 3.
The next time a row is inserted into Test the PK 3 is reused, and chaos follows.
How do you suggest I handle this problem? Is this something that you can\will fix in SQL Compare?
Thanks,
Brian
Comments
I agree with what your saying and I've logged this as a bug with SQL Compare. The SEED value should be preserved when the table needs to be rebuilt. Thanks for bringing this to our attention. The bug is logged under the code SC-4615.
This only seems to be a problem if you delete rows from the end of the table.
I will let you know when we find out if/when a fix will be avaliable.