Reseed an Identity column in SQL 2008

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.

Advertisements
This entry was posted in SQL and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s