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
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.
You have probably attempted to add the new column in between existing columns
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.
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.
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
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.
ALTER TABLE table_name ALTER COLUMN column_name DATA_TYPE [(COLUMN_SIZE)] NULL;
ALTER TABLE table_name ALTER COLUMN column_name DATA_TYPE [(COLUMN_SIZE)] NOT NULL;Categories Solutions, SQL, Tips
This site uses Akismet to reduce spam. Learn how your comment data is processed.
