By default, SQL server has added some limitations. For instance, changing data types in design mode, adding a column in the middle of two existing columns, and changing the null-ability of an existing column are not permitted to change from design mode in SQL server management studio (SSMS) because they require a table to be dropped and created again. Here is a solution for the error these create.
The following actions might require a table to be re-created as per msdn.
-
- 1. Adding a new column to the middle of the table
- 2. Dropping a column
- 3. Changing column null-ability
- 4. Changing the order of the columns
- 5. Changing the data type of a column
In the figure below, I tried to change the length of a column name from varchar(100) to varchar(150). It did not allow me to make this change and threw the error below.
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 cannot be re-created, or you have enabled the option Prevent saving changes that requires the table to be re-created.
SQL server has included a feature to allow users to edit tables in design mode.
Follow these steps to override the default settings and solve the error “Saving changes is not permitted.”
1. In SSMS, go to Tools in the Main Menu.
2. Select Options from Tools.
3. Select and expand Designers.
4. Uncheck the box Prevent saving changes that require table re-creation.
5. Click OK. This will save your applied settings automatically.
Now, if you try to alter the table in design view, SQL server will accept your input and will not give the error saving changes is not permitted.
The best method for making changes in table structure is through T-SQL code, which works perfectly, and we can run same code on multiple servers if we have to make the same changes over multiple databases having the same table structure.
