You might have faced an error as shown below, when you make some changes in table from design mode in SQL server management studio (SSMS).
‘Saving changes is not permitted. The change you have made requires the following table to be dropped and re-created. You have either made changes to a table that can’t be recreated or enabled the option prevent saving changes that require the table to be re-created.’
It usually happens when you make any change that require the table to be dropped and re-created such as changing the column data type, Adding a column between two existing columns, changing the column nullability, or deleting a column from design mode in SSMS.
Lets see the case when an error occurs, and try to resolve that.
Suppose you have a EmployeeData table and you need to change the column data type size for EmpName column.
Lets do this using design mode in SQL server management studio (SSMS).
To open the table in design mode, just right click on table, and click on Design.
Once you click on Design, you will see a table is opened in edited mode as shown below.
Lets change the data type size of column EmpName from varchar(50) to varchar(100).
After making changes in data type size, when you try save the change by pressing Ctrl +S.
You will get an error message as shown below.
Click on Cancel button to close error message dialog box.
You can resolve this error by changing the default table setting for editing a table from design mode.
Lets go step by step and change the setting for editing a table.
In SQL Server Management Studio (SSMS), Go to Tools then click on Option..
After that a Options dialog box opens, now expand the Designers, and select Tables and Database Designers.
Now on the right side, you will see a table options there uncheck the box Prevent saving changes that require table re-creation.
Also Note that: By unchecking this option, when you change a table basically you alter the metadata structure of the table, and then save the table, the table must be re-created based on these changes. This may result in the loss of metadata and in a direct loss of data during the re-creation of the table. So it is recommended that to use T-SQL alter table command for modifying a table definition such as altering, adding, or dropping columns and constraints.
After that click OK to save the settings.
After making changes in setting, Lets try to change the data type size again.
You can see, this time it does not return any error and changes in table are saved.
Also Read..
Get Dark theme in SQL Server Management studio
Get the detailed information of all datatypes in SQL Server
