I am using SQL server 2012 and when trying to edit design of already saved table, i am getting this error
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.
Here is the image of the error
I can't drop and re-create table alwyas just to change the a column from int to varchar, So how can I remove this warning/error permanently and save table with changes?
You can disable this warning in your sql server management studio by following these steps:
Here is the screenshot which may help
This error occurs as by default SQL Server Management Studio prevents the dropping of tables because when a table is dropped its data contents are lost as when altering a column's datatype in the table Design view and saving the changes, drops the table internally and then re-creates a new one.
According to https://docs.microsoft.com/en-us/sql/ssms/visual-db-tools/save-not-permitted-dialog-box?view=sql-server-2017
The Save (Not Permitted) dialog box warns you that saving changes is not permitted because the changes you have made require the listed tables to be dropped and re-created.
The following actions might require a table to be re-created:
Adding a new column to the middle of the table
Dropping a column
Changing column nullability
Changing the order of the columns
Changing the data type of a column
If you want to remove the warning message open your SQl server Management studio(SSMS) and navigate to the menu Tools >> Options >> Designers and uncheck “Prevent Saving changes that require table re-creation”
That's it, you are done.
Subscribe to our weekly Newsletter & Keep getting latest article/questions in your inbox weekly