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

Question

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.

Possible duplicate of [Saving changes after table edit in SQL Server Management Studio](https://stackoverflow.com/questions/1969096/saving-changes-after-table-edit-in-sql-server-management-studio) — Liam, Jul 10 '17 at 14:59

score 1717 · Accepted Answer · edited Jun 20 '20 at 09:12

1717

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 Colt Kwong Blog Entry:
Saving changes is not permitted in SQL 2008 Management Studio

edited Jun 20 '20 at 09:12

Community

1
1

answered Jul 24 '11 at 23:26

Robert Harvey

178,213
47
333
501

1

thanks ,,,, it works ,,,, could this change make a problem to my database or the edited table ? or its safe ? – Arash Jul 24 '11 at 23:30
7

SSMS shouldn't let you do something unsafe without warning you first. But have a backup on hand, just in case. – Robert Harvey Jul 24 '11 at 23:31
52

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. – Justin King Sep 28 '11 at 00:45
18

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! – NoWar Apr 25 '13 at 13:11
8

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! – Shawn J. Molloy Apr 12 '14 at 06:50
8

Here is Microsoft's CAVEAT on disabling the 'Prevent saving changes...' option: https://support.microsoft.com/en-us/kb/956176. Basically, you can lose metadata like Change Tracking, if that's a feature you use. – Baodad Oct 07 '15 at 22:53
1

Sometimes it would be great to just have better feedback to the user. Like I will start on a new contract job and I don't know what permissions I have been given to some database, so when I go to change varchar(20) to varchar(60) because of a business requirement, well I know that this answer does not apply to me, but instead , my issue is just a stupid default buried in the menu and thus @ypercube answer is what works for me. I think that this answer if suffice for a lot of people, but ypercube answer is certainly good for a decent % of users too. – Tom Stickel May 23 '16 at 20:05
Yes, this works. It would be more useful if Microsoft told you what to do to enable this instead of just telling you that you can't do it. Of course it should be available only to people who know what they are doing (in my case a developer with a new table but I want to keep what has already been saved into it). – CashCow Mar 20 '17 at 11:02
2

You should add a recommendation for all but experienced users to set this option back right after the desired change is done. This can cause a lot of damage. – MosheG May 01 '17 at 09:51
1

Just a warning. I tested this setting on my SQL server, and found that if you disable the "warning" and reduce the nvarchar size of a field, you don't get any warning that your existing data won't fit in the new layout. It is just clipped to the new size. So if you are used to get those errors in for example MySQL, don't expect to get them in MS-SQL Server. – Bob Dec 02 '19 at 16:58
Why is this option there ? For security purposes ? – MindRoasterMir Nov 05 '20 at 16:34
@MindRoasterMir: Because some actions require dropping and recreating tables, a potentially dangerous operation. – Robert Harvey Nov 05 '20 at 17:03
That's Nice! But you are created the relationship than all relationship lost <> – Junaid khan Sep 06 '21 at 12:12
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. https://learn.microsoft.com/en-us/troubleshoot/sql/ssms/error-when-you-save-table To work around this problem, use ALTER TABLE Transact-SQL statements to make the changes to the metadata structure of a table. – msn.secret Oct 28 '22 at 03:01

score 300 · Answer 2 · edited Dec 28 '22 at 16:48

300

If you are using SSMS:

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

edited Dec 28 '22 at 16:48

Mohammadreza Khedri

2,523
1
11
22

answered Jul 24 '11 at 23:27

ypercubeᵀᴹ

113,259
19
174
235

7

@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! – ypercubeᵀᴹ Jan 18 '18 at 11:14
will the relationships between tables be lost if the tables are re-created like this? – Neil Meyer Jan 18 '18 at 11:20
1

@NeilMeyer not sure what exact statement SSMS will produce. It will probably recreate any dropped FK as well. – ypercubeᵀᴹ Jan 18 '18 at 11:21
How stupid is it that there is even no warning any more when you disable this option. – ESP32 Jan 21 '21 at 08:07
5

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 – Geoff Griswald Apr 14 '21 at 13:45

WonderWorker · Answer 3 · 2017-06-06T12:55:58.740

105

Prevent saving changes that require table re-creation

Five swift clicks

Tools

Options

Designers

Prevent saving changes that require table re-creation

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.

edited Jun 06 '17 at 12:55

answered Jul 01 '15 at 10:42

WonderWorker

8,539
4
63
74

4

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. – Geoff Griswald Apr 14 '21 at 13:46
1

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. https://learn.microsoft.com/en-US/troubleshoot/sql/ssms/error-when-you-save-table – Geoff Griswald Apr 14 '21 at 13:51

score 54 · Answer 4 · answered Nov 05 '18 at 14:05

54

This can be changed easily in Microsoft SQL Server.

Open Microsoft SQL Server Management Studio 2008

Click Tools menu

Click Options

Select Designers

Uncheck "Prevent saving changes that require table re-creation"

Click OK

answered Nov 05 '18 at 14:05

Irshad Ahmed Akhonzada

1,340
13
23

score 34 · Answer 5 · edited Aug 29 '16 at 10:41

34

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.

edited Aug 29 '16 at 10:41

Martijn Pieters

1,048,767
296
4,058
3,343

answered Jan 18 '16 at 10:15

Tabish Usman

3,110
2
17
15

Table designer is handy, but you can not achieve everything with it – Sitecore Sam Jul 04 '18 at 07:56

score 23 · Answer 6 · answered Jan 22 '14 at 11:03

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:

Open Microsoft SQL Server Management Studio 2008
Click Tools menu options, then click Options
Select Designers
Uncheck "prevent saving changes that require table re-creation" option
Click OK
Try to alter your table
Your changes will performed as desired

how does this add to the accepted answer in any way? – Immortal Blue Oct 28 '15 at 10:16 — Immortal Blue, Oct 28 '15 at 10:16

score 18 · Answer 7 · edited Apr 22 '13 at 09:11

18

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.

edited Apr 22 '13 at 09:11

Andrew Barber

39,603
20
94
123

answered Apr 14 '13 at 15:39

FIFO BIZSOL

729
6
6

score 14 · Answer 8 · edited Jun 06 '17 at 09:31

14

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

SQL Server 2012 example:

edited Jun 06 '17 at 09:31

WonderWorker

8,539
4
63
74

answered Aug 27 '16 at 05:44

Pedram

6,256
10
65
87

score 12 · Answer 9 · edited Sep 30 '15 at 05:41

12

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 "

edited Sep 30 '15 at 05:41

Panther

3,312
9
27
50

answered Sep 30 '15 at 05:25

Panagiotis

131
1
2

2

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! – stitz Jan 28 '19 at 05:27
The tragedy here is that SSMS should be doing exactly this under the hood and thus not require dropping of the table – m12lrpv Apr 30 '19 at 00:00
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. – Geoff Griswald Apr 14 '21 at 13:53
Recent "MSDN" link: https://learn.microsoft.com/en-us/troubleshoot/sql/ssms/error-when-you-save-table – Hans Keﬆing Oct 07 '22 at 08:50

score 7 · Answer 10 · answered Mar 21 '16 at 07:47

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...

score 2 · Answer 11 · answered Dec 18 '15 at 10:54

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.

score 1 · Answer 12 · answered Aug 27 '17 at 12:06

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

answered Aug 27 '17 at 12:06

Ogbonna Vitalis

7,969
2
11
21

score 1 · Answer 13 · answered Aug 09 '18 at 11:24

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

answered Aug 09 '18 at 11:24

Taher Chtaywi

31
3

2

This is just a repeat of many of the existing answers. – Pang Aug 03 '20 at 07:25

score 0 · Answer 14 · answered May 14 '18 at 15:51

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.

Ahmad Pujianto · Answer 15 · 2021-05-04T03:12:34.343

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.

edited May 04 '21 at 03:12

answered Sep 08 '20 at 15:07

Ahmad Pujianto

309
1
3
11

1

It will be helpful for future users if you can tell why SSMS is blocking the action, and what settings should be changed. – Kristianne Nerona Sep 08 '20 at 22:57
1

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. – Geoff Griswald Apr 14 '21 at 13:56

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

15 Answers15

Linked

Related