Articles

Saving changes is not permitted, and why turning the warning off is the wrong fix

You change something in the table designer, press save, and get:

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.

The first thing worth knowing: SQL Server never saw this. It is Management Studio refusing, not the database engine. The change may be perfectly legal — it is the way the designer intends to carry it out that the warning is about.

What the designer was about to do

Some changes cannot be written as an ALTER TABLE. Moving a column, for instance: there is no statement for it.

ALTER TABLE dbo.Customer ALTER COLUMN Country SET ORDER 2;
Msg 156, Level 15, State 1
Incorrect syntax near the keyword 'SET'.

So when the designer meets one of those, it does the only thing available: builds a new table with the shape you asked for, copies every row across, drops the original, and renames the new one into its place.

That works. It is also a very different operation from the one you thought you were asking for.

What a rebuild actually costs

The new table is not the old table. It has a new identity in the database, which is easy to show:

SELECT OBJECT_ID('dbo.Customer') AS before_rebuild;

-- what the designer does, in four statements
EXEC sp_rename 'dbo.Customer', 'Customer_old';

CREATE TABLE dbo.Customer (
    CustId  int IDENTITY(1,1) PRIMARY KEY,
    Name    varchar(50) NULL,
    Country varchar(50) NULL,     -- the column somebody wanted in the middle
    City    varchar(50) NULL);

SET IDENTITY_INSERT dbo.Customer ON;
INSERT dbo.Customer (CustId, Name, Country, City)
SELECT CustId, Name, NULL, City FROM dbo.Customer_old;
SET IDENTITY_INSERT dbo.Customer OFF;

DROP TABLE dbo.Customer_old;

SELECT OBJECT_ID('dbo.Customer') AS after_rebuild;
before_rebuild
1205579333

after_rebuild
1237579447

A different object id is not a detail. Anything attached to the table rather than to its name is attached to the old one:

And the obvious ones: every row is copied, so on a large table this is slow, writes a great deal to the log, and holds locks the whole time. If it fails in the middle you are left with a renamed original and a half-filled copy.

Most of what people do in the designer needs none of this

This is the part the warning does not tell you. A lot of the changes that trigger it have a one-line ALTER that does not rebuild anything.

Making a column longer is metadata only and finishes instantly, whatever the table's size:

ALTER TABLE dbo.Customer ALTER COLUMN Name varchar(100) NULL;

Keep the rest of the definition as it was. ALTER COLUMN replaces the whole thing, so leaving out NOT NULL on a column that had it is how a column quietly becomes nullable.

Changing whether a column accepts nulls is the same, as long as the data already agrees — fill the empty rows first, or the alter is refused with a Msg 515 naming the column:

UPDATE dbo.Customer SET City = 'unknown' WHERE City IS NULL;

ALTER TABLE dbo.Customer ALTER COLUMN City varchar(50) NOT NULL;

Adding a column is a single statement. It lands at the end of the table, and that is the only place it can go:

ALTER TABLE dbo.Customer ADD Country varchar(50) NULL;
column_id  name
1          CustId
2          Name
3          City
4          Country

The changes that genuinely need a rebuild

Three, mainly: moving a column, inserting a column anywhere but the end, and adding or removing IDENTITY on an existing column.

Before you pay for one, ask whether you want the change at all. Column order is the common case, and it is cosmetic. Nothing in SQL Server cares what order the columns are in — except SELECT *, which has its own problems and should not be the thing deciding your table's physical layout.

IDENTITY is the other one, and it cannot be added with ALTER at all — see why, and what to do instead.

The checkbox

The warning names it, so people go and find it: Tools → Options → Designers → Table and Database Designers → Prevent saving changes that require table re-creation.

Clearing it does not make the change safe. It stops the designer asking, and then it rebuilds the table anyway — the same copy, the same drop, the same new object id, with no warning. On a small table in development that is fine, and it is why so many answers online say to untick it. On a table with millions of rows, or one with permissions granted on it, it quietly does the thing the checkbox existed to warn you about.

Leave it on. It is the only notice you get that an innocent-looking edit is about to rewrite a table.

What to do instead

  1. Write the ALTER. Most of the time there is one, and it is faster than the designer.
  2. If there isn't one, ask whether the change is worth a rebuild. Column order usually is not.
  3. If it is, script it yourself so you can read it before it runs. The designer will give you its version: make your change, then use Generate Change Script rather than Save. Read it, and notice how much of it is INSERT INTO … SELECT.

Seeing the script before anything runs

In WoodFireERD, changes are made to your copy of the schema and nothing reaches the database on its own. Edit a column in the Columns section, then open Pending changes: the migration script is there to read, run when it suits you, or keep.

Where a change cannot be made with a single statement, the script says so as a step rather than generating something that would fail — so the choice between an ALTER and a rebuild is one you make with both in front of you, rather than one a checkbox makes on your behalf.

An unhandled error has occurred. Reload 🗙