Monday , December 10 2018
Home / DataBase / SQL SERVER / How to alter table in DesignMode in SQL Server

How to alter table in DesignMode in SQL Server

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

sqlserver_create_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

SqlServer_Create_Error_soluction

 

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.

Source : https://support.microsoft.com/en-in/kb/956176

Thanks for reading this article if you like this article please share with your friends

Print Friendly, PDF & Email

Give us your valuable feedback

comments

About Murali

Hello everyone I am Software Developer having 3+ years of Experience in Microsoft Technologies and Others. My skills :- Asp.net, C#,Ado.net, SQL SERVER, web services, windows Service,Windows applications,HTML,CSS, JavaScript,JQuery,AnjularJs

Check Also

sql server backup script

Backup all databases in SQL Instance with one script

Backup: A copy of SQL Server data that can be used to restore and recover the …