Microsoft SQL – Saving changes is not permitted (SSMS Design Mode)

Have you ever attempted to alter the design on a Microsoft SQL table using the Microsoft SQL Server Management Studio (SSMS) tool. Maybe, to add an additional column, or change an existing columns data type, only to be greeted by the below message

Jump to

Saving changes is not permitted. The changes you have made require the following tables to be drooped 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

As the message states SQL Management Studio will not let you make the schema change without dropping and recreating the table. But what if you don’t want to drop the table and loose the data?

In this short post I will cover some alternative methods that will allow you to modify a tables schema without having to drop the table.

Adding an additional column

You have probably attempted to add the new column in between existing columns

Option 1 – Using SSMS

SSMS design mode will allow you to add a new column at the very end/bottom. You will first need to exist design mode disregarding your changes. Reopen design mode and add your new column to the very bottom.

Option 2 – Using T-SQL

Rather than using the design mode you can add you need column using a query, however you can’t specify the position using a query. So regardless of if you use option 1 of option2 your new column will be added to the end. As a side note it is possible to specify a new columns position in MySQL using the INT AFTER command.

ALTER TABLE table_name

Regardless of where a columns position is created you can specify the order you want to view them in by ordering them correctly in your SELECT statement.

Deleting an existing column

You will be able to delete a column and save the schema change using SSMS without receiving the “Saving changes is not permitted” message. However, you can also delete a column using a query if you prefer. Useful if you are making multiple changes in one go.

ALTER TABLE table_name
DROP COLUMN column_name

Changing the Allow Null attribute

You will not be able to change if a column allows a null of not via the SSMS design mode. But instead you can use a query to change the attribute.

You could also use this method to change the data type but, only if the table’s data is compatible with the new datatype i.e. a can become a varchar. But a marcher can’t become and int.

To set to allow nulls

ALTER TABLE table_name

To set to not allow nulls

ALTER TABLE table_name

Did this post help you out? Please consider supporting this site by donating on our Ko-fi page. Thanks

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.