Alter Columns DataType Without Dropping Table 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 by 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 it works as expected.

 

 

 

So this is one option using a SQL query.

 

Option 2: Disable "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 option window expand Designers and under that "Table and Database Designers" and 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 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 like this.

 

Thank you.

Happy Coding.