How to alter table in DesignMode in SQL Server:
Suppose we already created a table in sql server now we want to alter table like add additional column in table or we need to remove existing column from table.
If we perform above tasks from query window then we can run without any problem but if we run from Design window then we can get below error
For Example I have created a table called Student with three Fields(Sno,Sname,Age) now i want to add another column called Dob. I want to add this column from Design Mode.
After adding column when i click save then i will get above error.
to resolve above error we need to disable below option
Tools>Option>Designers>Table and Database Designers>
uncheck Prevent saving changes that require table re-creation
now when you alter table in design mode then it will execute properly.
Note If you disable this option, you are not warned when you save the table that the changes that you made have changed the metadata structure of the table. In this case, data loss may occur when you save the table.
Risk of turning off the “Prevent saving changes that require table re-creation” option
Although turning off this option can help you avoid re-creating a table, it can also lead to changes being lost. For example, suppose that you enable the Change Tracking feature in SQL Server 2008 to track changes to the table. When you perform an operation that causes the table to be re-created, you receive the error message that is mentioned in the “Symptoms” section. However, if you turn off this option, the existing change tracking information is deleted when the table is re-created. Therefore, we recommend that you do not work around this problem by turning off the option.
Thanks for reading this article if you like this article please share with your friends