Alter Columns DataType Without Dropping Table in SQL

Introduction

In this article, we will learn about altering columns DataType Without Dropping Table in SQL. For learning more about tables in SQL, Please go through Tables in SQL.

First of all, we will create a table named tblManager using the following query. 

Create table tblManager   
(  
    ID int primary key identity,  
    Name nvarchar(50),  
    Gender nvarchar(50),  
    Salary nvarchar(50)  
)  

Notice that the Salary column's datatype is nvarchar. Here this table has the columns ID, Name, Gender, and Salary.

Now we will write the SQL query and insert some sample data into the tblManager table.

Insert into tblManager values('Shaili Dashora','Female','30000')  
Insert into tblManager values('Sourabh Somani','Male','40000')  
Insert into tblManager values('Bhumika Dashora','Female','20000')  
Insert into tblManager values('Raj Jain','Male','20000')  
Insert into tblManager values('Rajendra Dashora','Male','40000')  

Now we will see the table data look like this.

Now we want to write the query to list the total salaries of the Managers grouped by Gender. So we want output like this.

So we will write the following query for that.

Select Gender,SUM(Salary) as Total  
from tblManager  
Group by Gender  

Here keep in mind that the Salary column DataType is nvarchar.

Let's try to execute this and see what happens.

Here the error message says "Operand data type nvarchar is invalid for sum operator" So we cannot use the Salary column with the SUM aggregate function, because its DataType is nvarchar. So we need to change the DataType of this column from nvarchar to integer.

Step 1, Now one way to do that is within the Object Explorer. Right-click on the table tblManager then click on the Design option.

Notice that the DataType of the Salary column is nvarchar.

Now let's change it to an int like this.

Now save it and see what happens. We will get a warning like this.

Basically, it says that we need to drop and re-create the table, but we have some data in this table so if we drop and re-create the table we lose the data.

So how do we eliminate this warning message? Basically, there are two ways.

Option 1. Use a SQL query to alter the column as shown below. So instead of using the SQL Server designer, we can simply use a SQL query.

Alter Table tblManager  
Alter column Salary int  

Now after the execution of this query we will execute the previous query to list the total salaries of Managers grouped by Gender like this:

Select Gender,SUM(Salary) as Total  
from tblManager  
Group by Gender  

Now we will see if it works as expected.

So this is one option using a SQL query.

Option 2. Disable the "Prevent saving changes that require table re-creation" option within SQLServer, this option is ON by default within SQL Server so we need to disable this option.

So to do that go to SQL Server and within Tools select Options.

Now in the options window expand Designers and under "Table and Database Designers" uncheck the check box "Prevent saving changes that require table re-creation" then click OK.

Now we will open the Design view of the table and here the DataType of the Salary column is int.

Now maybe we change its DataType int to decimal(8,2) like this.

Now we will save the changes and we will see if we get a warning but it is fine when we click on Yes the changes are saved.

Now we execute the query and we get the same output as this.

Conclusion

In this article, we learned about alter columns DataType without dropping the table in SQL.