In SQL 2000/2005, I used to be able to change the seed of an identity field in table designer. It will just prompt a message saying this table and a few other table (if having FK on) will be modified. However somehow in SQL 2008, I can not do that any more. The table seems can not be dropped.
I found the workaround:
dbcc checkident (my_table, reseed, 1052)
This will raise the next identity value in my_table to be 1052. It does NOT change the column definition but change the run-time seed instead.
You have to make sure the new seed value is greater than all existing values. Otherwise you will get UNIQUE violation errors.
Also to temporarily allow inserting values into the identity column, you can do this:
set identity_insert mytable on
and this will turn it off
set identity_insert mytable off
But you can only run it on one table at a time.
P.S. I figured out the reason why SQL 2008 will report “Saving changes is not permitted. ..” error when trying to change the identity properties. By default, in SQL 2008 Management Studio, Tools->Options->Designers->Table and Database Designer, “Prevent saving changes that require table re-creation” is checked. Changing identify field will internally drop and re-create the table. Just uncheck it and you can change it in UI.
