Have you been working on SQL Server when you realize that you need a new column in a table or make any kind of change like allow null for a specific column ?
The easy and fast way to make a change is using the design option to add the new column. Most of the time it allows you to introduce the name and the DataType you need to create.
By default SQL server, is going to show you an warning message when you committed the changes you have made in the table. The warning doesn’t allow you to save the changes and show you the follow message: “Saving change is not permitted”. The change you have made require the following tables to be dropped and re-create. You have either made changes that cant be recreate or enable the option prevent saving changes that require the table to be re-create.
This happens because SQL has activated an option by default, which doesn’t allow you to do that. in this post I am going to Show you how to change that option. However you have to be careful changing this and I recommend you just do it in a developer environment, not in production environment.
So, to change that go to Tool – Designers and Prevent saving change that require table re-creation as show the next figure: