Change Table Structure in SQl Server 2008

3
votes

I am trying to change a design of a table in SQL Server 2008, but I am getting an error stating that I need to drop and recreate the table.How Can I Solve This Problem.

Saving Changes is Not Permitted.The Changes you Have Made require the following tables to be dropped and re-created.you have either made changes to a table that can't be re-created or enabled the option prevent saving changes that require the table to be re-created.

3
sql-server-2008
What exactly are you trying to do, and what is the full error message? - Blorgbeard
You need to disable the option in SSMS "prevent saving changes that require the table to be recreated" as suggested in the error message. But what are you actually trying to do? Sometimes the GUI does unnecessary table recreations for things which could be done via ALTER TABLE .. ALTER COLUMN - Martin Smith

3 Answers

10
votes

Go to: Tools - Options - Designers - Table and Database Designers and de-select "Prevent saving changes that requiere table re-creation"

0
votes

You can do most things with ALTER TABLE. Changing a datatype, nullability, adding/removing most constraints, adding/removing columns (ignoring order) should not require a table rebuild.

The GUI designer will ask for a table rebuild if you reorder columns, or add IDENTITY.

0
votes

As Pcofre has said the answer is: click Tools ---> Options ----> Designer ----> on the right pane find the option meaning prevent the tables from being recreated then Remove its tick