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.