Alter the Table Columns without Dropping it

This blog explains the following,
  • How to create the table
  • How to alter the table (How add/modify Column/DataType in existing table)
  • Ways to prevent Saving changes is not permitted in SQL Server
Step 1: Create table
use the following sql script to create Mas_Employee table.
  1. Create table Mas_Employee(  
  2.    ID int primary key,  
  3.    Name varchar(50),  
  4.    Salary int,  
  5.    DeptID  varchar(10)  
  6. )  
Step 2: Alter the table
For example, If you want to change the DataType of DeptId from Varchar(10) to int do the following steps:
  1. Goto "Object Explorer" window.
  2. Your Database.
  3. Tables.
  4. Right click on "Mas_Employee".
  5. And select "Design".
  6. Change the datatype from varchar(50) to int.
  7. Save the table.

 At this point, you will get an error message as in the following.

 
Step 3: Ways to prevent Saving changes is not permitted in SQL Server
There are two ways to prevent the above error.
i ) Alter the table by using Sql Query.
ii) Disable "Prevent saving changes that require table re-creation" option.
 
Method 1: Alter table by using Sql Query
For example to add new column (Gender) in the existing table (Mas_Employee) use the below script

  1. Alter table Mas_Employee  
  2. Alter column Gender varchar(40)  

Method 2 : Disable "Prevent saving changes that require table re-creation" option to prevent the error.
  • Goto Tools, select Options.
  • Expand Designers, and select "Table and Database Designers".
  • Then uncheck "Prevent saving changes that require table re-creation".
  • Click OK.
 
I hope you enjoyed it.