990

When I create a table in SQL Server and save it, if I try to edit the table design, like change a column type from int to real, I get this error:

Saving changes is not permitted. The change you have made requires the following table to be dropped and re-created. You have either made changes to a table that can't be recreated or enabled the option prevent saving changes that require the table to be re-created.

Why do I have to re-create the table? I just want to change a data type from smallint to real.

The table is empty, and I didn't use it until now.

Improve this question
1

15 Answers 15

Reset to default
1815

From Save (Not Permitted) Dialog Box on MSDN :

The Save (Not Permitted) dialog box warns you that saving changes is not permitted because the changes you have made require the listed tables to be dropped and re-created.

The following actions might require a table to be re-created:

  • Adding a new column to the middle of the table
  • Dropping a column
  • Changing column nullability
  • Changing the order of the columns
  • Changing the data type of a column <<<<

To change this option, on the Tools menu, click Options, expand Designers, and then click Table and Database Designers. Select or clear the Prevent saving changes that require the table to be re-created check box.

See Also
Saving changes is not permitted error message in SSMS

Improve this answer
Sign up to request clarification or add additional context in comments.

14 Comments

SSMS shouldn't let you do something unsafe without warning you first. But have a backup on hand, just in case.
This option tells SSMS that it can go ahead and create a Temp Table in new schema, copy data into this, then delete old table and rename the Temp Table back to original name. All fine to do as should be in transaction, however relationships will be broken and recreated so if not 100% then could do something unexpected.
Don't know why I cannot remember those simple steps to drop down this option and I have always to search for the solution. Hehehehehe. Thanks man!
Holy cow - you can just turn this off to get past this error?? I did not know this, and for the past 3 years have lived in fear of inadvertenly designing a table without a Identity Specification and having to create a new table. Great tip!
Here is Microsoft's CAVEAT on disabling the 'Prevent saving changes...' option: support.microsoft.com/en-us/kb/956176. Basically, you can lose metadata like Change Tracking, if that's a feature you use.
|
349

If you are using SSMS:

Go to the menu Tools >> Options >> Designers and uncheck Prevent Saving changes that require table re-creation

Improve this answer

7 Comments

@NeilMeyer beware that the check box is checked there for a reason. It's a safety measure to prevent operations that will drop and recreate a table (and thus losing all its data! So, use with caution!
will the relationships between tables be lost if the tables are re-created like this?
@NeilMeyer not sure what exact statement SSMS will produce. It will probably recreate any dropped FK as well.
It's not stupid, because you run very little risk of losing anything important. ypercube above is wrong, you don't lose any data. It does drop the old table and create a new one for you, but it makes sure to preserve all the data. The only issue might be if you edited a very large table with hundreds of millions of rows in this way, it may take an hour or two to save your changes
yup, just did it and confirming that all the data was preserved. but I initially upvoted ypercubeᵀᴹ's comment and can't undo it cz 60s already passed lmao
|
119

Prevent saving changes that require table re-creation

Five swift clicks

Prevent saving changes that require table re-creation in five clicks

  1. Tools
  2. Options
  3. Designers
  4. Prevent saving changes that require table re-creation
  5. OK.

After saving, repeat the proceudure to re-tick the box. This safe-guards against accidental data loss.

Further explanation

  • By default SQL Server Management Studio prevents the dropping of tables, because when a table is dropped its data contents are lost.*

  • When altering a column's datatype in the table Design view, when saving the changes the database drops the table internally and then re-creates a new one.

*Your specific circumstances will not pose a consequence since your table is empty. I provide this explanation entirely to improve your understanding of the procedure.

Improve this answer

2 Comments

This is wrong. When you edit a table like this and it has data in it, data is not lost. The new table will be created with all the data from the original table inside it.
Any indexes, default values, constraints, triggers etc. will also be maintained. As per Microsoft docs, the only thing you will lose is Change Tracking, a feature which very few people use or enable. learn.microsoft.com/en-US/troubleshoot/sql/ssms/…
62

This can be changed easily in Microsoft SQL Server.

  1. Open Microsoft SQL Server Management Studio 2008
  2. Click Tools menu
  3. Click Options
  4. Select Designers
  5. Uncheck "Prevent saving changes that require table re-creation"
  6. Click OK

enter image description here

Improve this answer

Comments

38

To change the Prevent saving changes that require the table re-creation option, follow these steps:

Open SQL Server Management Studio (SSMS). On the Tools menu, click Options.

In the navigation pane of the Options window, click Designers.

Select or clear the Prevent saving changes that require the table re-creation check box, and then click OK.

Note: If you disable this option, you are not warned when you save the table that the changes that you made have changed the metadata structure of the table. In this case, data loss may occur when you save the table.

enter image description here

Improve this answer

1 Comment

Table designer is handy, but you can not achieve everything with it
25

It is very easy and simple setting problem that can be fixed in 5 seconds by following these steps

To allow you to save changes after you alter table, Please follow these steps for your sql setting:

  1. Open Microsoft SQL Server Management Studio 2008
  2. Click Tools menu options, then click Options
  3. Select Designers
  4. Uncheck "prevent saving changes that require table re-creation" option
  5. Click OK
  6. Try to alter your table
  7. Your changes will performed as desired
Improve this answer

1 Comment

how does this add to the accepted answer in any way?
19

Go on Tool located at top menu.
Choose options from dropdown.You have a popup now select Designers option located on left hand block of menus. Uncheck the option Prevent saving changes that require table re-creation. Click on OK Button.

Improve this answer

Comments

17

Un-tick the Prevent saving changes that require table re-creation box from Tools ► Options ► Designers tab.

SQL Server 2012 example:

enter image description here

Improve this answer

Comments

13

Copied from this link " ... Important We strongly recommend that you do not work around this problem by turning off the Prevent saving changes that require table re-creation option. For more information about the risks of turning off this option, see the "More information" section. ''

" ...To work around this problem, use Transact-SQL statements to make the changes to the metadata structure of a table. For additional information refer to the following topic in SQL Server Books Online

For example, to change MyDate column of type datetime in at table called MyTable to accept NULL values you can use:

alter table MyTable alter column MyDate7 datetime NULL "

Improve this answer

4 Comments

With so many answers all saying the same thing, this was the answer I was looking for since I had live data in my table I did not want to loose. I needed to update a decimal column from 0 decimal places to 2 decimal places and the simple alter statement worked perfect!
The tragedy here is that SSMS should be doing exactly this under the hood and thus not require dropping of the table
Ridiculous to quote the recommendation from MS to not do this, but not explain why. There are two reasons I would not use this: 1: My table was so large that the operation to drop and recreate it would take hours, so better to run it overnight as a job that doesn't rely on SSMS. 2: I was using Change Tracking on the table. The only thing you ever lose by using the SSMS "Edit Table" function with the safety off is Change Tracking.
Recent "MSDN" link: learn.microsoft.com/en-us/troubleshoot/sql/ssms/…
7

And just in case someone here is also not paying attention (like me):

For Microsoft SQL Server 2012, in the options dialogue box, there is a sneaky little check box that APPARENTLY hides all other setting. Although I got to say that I have missed that little monster all this time!!!

After that, you may proceed with the steps, designer, uncheck prevent saving blah blah blah...

sneaky_check_box_in_option

Improve this answer

Comments

2

1) Open tool which is on top.
2) Choose options from Picklist.
3) Now Comes the popup and you can now select designers option from the list of menus on the left side.
4) Now prevent saving changes need to be unchecked that needed table re-creation. Now Click OK.

Improve this answer

Comments

1

From the Tools menu, click on Options, select Designers from the side menu and untick prevent changes that can lead to recreation of a table. Then save the changes

Improve this answer

Comments

1

If you use sql server Management studio go to Tools >> Options >> Designers and uncheck “Prevent Saving changes that require table re-creation” It works with me

Improve this answer

1 Comment

This is just a repeat of many of the existing answers.
0

If you can not see the "Prevent saving changes that required table re-creation" in list like that The image

You need to enable change tracking.

  • Right click on your database and click Properties
  • Click change tracking and make it enable
  • Go Tools -> Options -> Designer again and uncheck it.
Improve this answer

Comments

0

Actually, You are blocked by SSMS not the SQL Server.

The solution are either change setting of SSMS or use a SQL query.

Using SQL Query you could do the update freely. Example you want to add a new column to a table, you could do like this :

ALTER TABLE Customers ADD Email varchar(255) NOT NULL DEFAULT 'OK';

Other option is changing SSMS setting. Please refer to other answer, as many has explain it.

Improve this answer

2 Comments

It will be helpful for future users if you can tell why SSMS is blocking the action, and what settings should be changed.
SSMS blocks the action for safety, since if it didn't block the action you might lose change tracking on the table, or the operation might take a long time to complete. If you're happy with both of those facts, then you can safely use the "Edit table" function in SSMS with the safety switch disabled.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.