Prevent saving changes that require table re creation

Saving changes is not permitted error message in SSMS

This article helps you work around the problem in which you receive an error message when you try to save a table in SQL Server Management Studio (SSMS).

Original product version: В SQL Server
Original KB number: В 956176

Symptoms

When you try to save a table after making changes to the table using Designer in SQL Server management Studio, you may receive the following error message:

Saving changes is not permitted. The changes that 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.

This problem occurs when you make one or more of the following changes to the table:

Cause

This problem occurs because the Prevent saving changes that require the table re-creation  option is enabled by default in SQL Server Management Studio.

When you change a table so that you alter the metadata structure of the table, and then you save the table, the table must be re-created based on these changes. This may result in the loss of metadata and in a direct loss of data during the re-creation of the table. If you enable the Prevent saving changes that require the table re-creation option in the Designer section of the SQL Server Management Studio (SSMS) Options window, you receive the error message that is mentioned in the ‘Symptoms’ section.

Workaround

To work around this problem, use ALTER TABLE Transact-SQL statements to make the changes to the metadata structure of a table.

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

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.

More information

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

Open SQL Server Management Studio.

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.

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.

Risk of turning off the «Prevent saving changes that require table re-creation» option

Although turning off this option can help you avoid re-creating a table, it can also lead to changes being lost. For example, suppose that you enable the Change Tracking feature in SQL Server to track changes to the table. When you perform an operation that causes the table to be re-created, you receive the error message that is mentioned in the Symptoms section. However, if you turn off this option, the existing change tracking information is deleted when the table is re-created. Therefore, we recommend that you do not work around this problem by turning off the option.

To determine whether the Change Tracking feature is enabled for a table, follow these steps:

When the Change Tracking feature is enabled, use Transact-SQL statements to change the metadata structure of the table.

Prevent saving changes that require table re creation

Часто в процессе работы с базами данных MS SQL нам нужно внести изменения в структуру ранее созданной таблицы, например, тип колонки таблицы. Если вы используете SQL Server Management Studio, то при выполнении данной задачи могут возникнуть некоторые трудности связанные с ошибкой «Saving changes is not permitted…». В этой статье я расскажу как легко решить эту проблему.

Представьте, что у нас есть таблица Customers с такими колонками:

С течением времени, вы замечаете, что длины 15 символов для колонки MobilePhone не достаточно, и желательно увеличить ее до 50. Первое логическое действие, это поправить это поле в дизайнере SQL Server Management Studio:

При попытке сохранения изменений, SQL Server Management Studio выдает нам следующее сообщение:

Что бы избежать вывода подобного сообщения, надо снять чекбокс «Prevent saving changes that require table re-creation» (Tools->Options):

После этого программа разрешит вам изменить тип колонки.

Если же вы не хотите менять никакие глобальные настройки программы, а просто сделать данное конкретное изменение, то есть и другой путь. Можно просто запустить SQL скрипт, выполняющий нужную операцию. Для поставленной нами задачи, этот скрип будет иметь следующий вид:

«Prevent saving changes that require the table to be re-created» negative effects

Preamble

I was modifying a column in SQL Server 2008 today, changing the datatype from something like currency(18,0) to (19,2).

I got the error «The changes you have made require the following tables to be dropped and re-created» from SQL Server.

Before you scramble to answer, please read the following:

Actual question

My actual question is for something else, as follows:

Are there any negative effects / possible drawbacks of doing this?

Does the table actually get dropped and recreated automatically when this box is unchecked?

If so, is the table copy a 100% exact replica of the source table?

5 Answers 5

Trending sort

Trending sort is based off of the default sorting method — by highest score — but it boosts votes that have happened recently, helping to surface more up-to-date answers.

It falls back to sorting by highest score if no posts are trending.

Switch to Trending sort

The table is only dropped and re-created in cases where that’s the only way SQL Server’s Management Studio has been programmed to know how to do it.

There are certainly cases where it will do that when it doesn’t need to, but there will also be cases where edits you make in Management Studio will not drop and re-create because it doesn’t have to.

The problem is that enumerating all of the cases and determining which side of the line they fall on will be quite tedious.

Are there any negative effects / possible drawbacks of doing this?

Does the table actually get dropped and recreated automatically when this box is unchecked?

It might. There is a laundry list of scenarios and the outcome is dependent on the version of SSMS, the version of SQL Server, and sometimes the edition. You can check by checking the box and trying to apply the change on a meaningless copy of the database first, but using actual ALTER TABLE scripts instead of the pointy-clicky GUI is the way to go IMHO.

If so, is the table copy a 100% exact replica of the source table?

Yes, if SSMS has to rebuild the table, it will be a 100% exact replica after it’s done (except for the change of course), but that could be next Wednesday. The process creates a new version of the table, copies all the data to it, then drop the old table and renames the new one.

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created [duplicate]

When I try to alter a data type of my table I get this horrible message from SQL Management Studion: «Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created». I already tried to do the modification by T-SQL and it worked, but why can’t I just do this by design mode? I’m using SQL Server 2008 R2.

3 Answers 3

Trending sort

Trending sort is based off of the default sorting method — by highest score — but it boosts votes that have happened recently, helping to surface more up-to-date answers.

It falls back to sorting by highest score if no posts are trending.

Switch to Trending sort

I would strongly suggest that you use T-SQL to make changes, or at the very least, preview the scripts that the Designers generate before committing them. However, if you want to do this in the designer, you can turn off that lock by going to Tools. Options. Designers..Table and Database Designers.. and unclick the «prevent saving changes that require table re-creation».

That lock is on by default for a reason; it keeps you from committing some change that is obfuscated by the designer.

EDIT: As noted in the comment below, you can’t preview the changes unless you disable the lock. My point is that if you want to use the table-designer to work on a table with this feature disabled, you should be sure to always preview the changes before committing them. In short, options are:

Sql Server ‘Saving changes is not permitted’ error ► Prevent saving changes that require table re-creation

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.

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

16 Answers 16

Trending sort

Trending sort is based off of the default sorting method — by highest score — but it boosts votes that have happened recently, helping to surface more up-to-date answers.

It falls back to sorting by highest score if no posts are trending.

Switch to Trending sort

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:

Do you use SSMS?

If yes, goto the menu Tools >> Options >> Designers and uncheck “Prevent Saving changes that require table re-creation”

Prevent saving changes that require table re-creation

Five swift clicks

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.