What are the challenges you face when working across database platforms? Take the survey
Options

Identity seed not preserved accross table rebuild.

nottinbenottinbe Posts: 6
edited February 9, 2010 1:22PM in SQL Compare Previous Versions
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

Comments

  • Options
    Thanks for your post.

    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.
    Chris
Sign In or Register to comment.