Cannot change a column in a table to be an Identity column

Question

Cannot change a column in a table to be an Identity column

Summer90

SSC-Dedicated

Points: 32891
More actions
January 11, 2010 at 8:02 am

#208928

In SQL Server 2008 I am using Management Studio to change a column to be an Identity, when I click save it states that I cannot do this.
Saving changes is not permitted. The changes you have made require the following tables to be dropped and recreated.
Why can't this no longer be done? I have done this countless times in SQL 2000. All I am doing is changing a column to be an Identity plain and simple. I use another tool called DBArtisian and it does this change without a problem.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

jcdyntek Ten Centuries Points: 1153 More actions · Answer 1

This is by design and can be quickly fixed in Management Studio by unchecking a property. To fix this in Management Studio, go to Tools -> Options then go to the Designer Page and uncheck "Prevent saving changes that require table re-creation".

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

There's an option in management studio to disallow changes that require dropping and recreating the table (which is exactly what management studio will do in this case). By default it is enabled. Probably added cause too many people tried doing just that on massive tables on production DB, locked the system and complained to MS that their tool caused problems.

You can disable the option easily enough, Tools -> Options.

Or, you can use a script ALTER TABLE ... ALTER COLUMN and just add the identity without having SSMS drop and recreate the entire table.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 3

Great, thanks a million to both of you. I found the option, Designers, Table & DB Designers Prevent Changes, removed check in box and I am able to make the change.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Do note that what SSMS is doing here is creating a new table, copying all the data over, recreating all the indexes and constraints and dropping the old table. Fine if the table's empty or has a small number of rows, terrible if there are millions of rows.

Be careful what you use the table designer for. Rather get familiar with the T-SQL commands

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 5

Oh yes, understood. The table was empty and apps. folks wanted it changed to be auto-increment.