Allow saving changes that require table re-creation while changing table design

Sometimes we have a situation that we need to change design of table either changing the size of column or type of column or we need to add new column into the existing table.

If we are doing table changes through wizard and we face an issue sometimes that SQL doesn't allow us to save the changes and give the restriction message like “saving changes is not permitted” because of either tables has data and it requires to be re created or something that not allowed with SQL rules.

See the below screen that comes when you change anything in table structure and trying to save. It not comes every time but sometimes it validates the table structure with internal mapping of type, constraints and data.

In above image we can see that it prevents to save table changes and gives the validation message that it needs to be recreated and the option to allow is not selected for this kind of message.

So here we should allow this by setting the option in SQL Server “Tool” menu.

Go to the Tool and then go to the Option, click on the Designer item in left tree then select the Table and Database Designer. We will get the right side some of the option as showing in to the below screen.

In above screen unchecked the “Prevent saving changes that require table re-creation” option and then click on OK.

After setting this option again try to save table structure changes, Now that saving permission message has been removed and it allow for the further changes.