You may get an error as follows when you want to change and save a column type or size in a table in the database.
Saving changes is not permitted.
To resolve this error, click “Tools-> Options-> Designers” via SSMS (SQL Server Management Studio). Then uncheck “Prevent saving changes that require table re-creation” and click OK.
There is one important point to note here. The reason that SQL Server blocks this processes by default is that if you want to make a change on a large production table and save it, and the process requires re-creation, the production system will remain locked until the table re-create operation is completed. So there are things you need to pay attention to when applying this solution.
1) You must do it at a time when the application is not running.
2)Before performing the operation, you should check the table size as follows.
If the table is a large table, you should not do this by pressing ok on SSMS. Because SSMS can have problems in this way. It could even be a loss of data.
Instead of making the changes you want to make over SSMS, click on the box in the image below to get the script of your process and run this script.
Table size is a relative concept. If the table is larger than 10 mb I do with the method of getting the script.
In addition, if the table is larger than 300 GB I do this in a different way.
Detailed information can be found in the article “What is Database File Group And How To Recreate Large Tables In Another File Group“.