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
ADD column_name DATA_TYPE [(COLUMN_SIZE)] NULL;
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
ALTER COLUMN column_name DATA_TYPE [(COLUMN_SIZE)] NULL;
To set to not allow nulls
ALTER TABLE table_name
ALTER COLUMN column_name DATA_TYPE [(COLUMN_SIZE)] NOT NULL;