How To Change Database Table Columns Without Dropping The Table

In this post, we will discuss changing database table columns without dropping the table. Let’s understand this with an example using the table TblDoctors.

Notice that this table has columns such as Id, DoctorName, Gender, Salary, and SpecializationId. I have used the following create table script to create that table. If you notice, the data type of the Salary column is nvarchar(50).

CREATE TABLE [dbo].[TblDoctors](
[Id] [int] IDENTITY(1,1) NOT NULL,
[DoctorName] [nvarchar](max) NOT NULL,
[Gender] [nvarchar](50) NULL,
[Salary] [nvarchar](50) NULL,
[SpecializationId] [int] NULL,
CONSTRAINT [PK_TblStudent] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

This is the SQL script to populate it with some sample data.

SET IDENTITY_INSERT [dbo].[TblDoctors] ON
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [Salary], [SpecializationId]) VALUES (1, N'Carter', N'Male', N'20000', 1)
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [Salary], [SpecializationId]) VALUES (3, N'Gianna', N'Female', N'10000', 1)
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [Salary], [SpecializationId]) VALUES (4, N'Brandon', N'Male', N'15000', 4)
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [Salary], [SpecializationId]) VALUES (5, N'Julia', N'Female', N'23000', 7)
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [Salary], [SpecializationId]) VALUES (6, N'Julian', N'Male', N'45000', 6)
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [Salary], [SpecializationId]) VALUES (7, N'Kayla', N'Female', N'25000', 5)
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [Salary], [SpecializationId]) VALUES (9, N'Autumn', N'Female', N'13000', 1)
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [Salary], [SpecializationId]) VALUES (10, N'Sebastian', N'Male', N'35000', 2)
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [Salary], [SpecializationId]) VALUES (11, N'Blake', N'Male', N'39000', 3)
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [Salary], [SpecializationId]) VALUES (12, N'Dr. Jacob', N'Male', N'41000', 4)
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [Salary], [SpecializationId]) VALUES (13, N'Dr. Henry', N'Male', N'28000', 5)
GO
INSERT [dbo].[TblDoctors] ([Id], [DoctorName], [Gender], [Salary], [SpecializationId]) VALUES (14, N'Dr. Tanmay', N'Male', N'36000', 1)
GO
SET IDENTITY_INSERT [dbo].[TblDoctors] OFF
GO

Let’s say, based on the table, we want to write a query that will list the total salaries of doctors grouped by gender. So, we want the output to resemble the image below.

So let’s write a query for that.

Select Gender, Sum(Salary) as Total
from TblDoctors
Group by Gender

if we try to execute the query, it will give an error.

So, basically, this error message is stating that we cannot use the salary column with the Sum() aggregate function because its data type is nvarchar. Therefore, we need to change the data type of the salary column from nvarchar to float, int, or decimal.

One way to do that is within the object explorer. Right-click on the table TblDoctors, click 'Design', and notice that the data type of the salary column is nvarchar. Let’s change it to 'Integer' and try to save it to see what happens.

We get a warning: 'Saving changes is not permitted.' Basically, this is telling us we need to drop and recreate the table.

And if you remember, this table already has some data as well. So if we drop and recreate it, we are going to lose that data. How do we get rid of this warning message?

Basically there are two ways.

1.We can use a SQL query to alter the column. Instead of using the SQL Server designer, we can simply use a SQL query.

Alter table TblDoctors
Alter column Salary float

So let’s execute that and see if it works as expected. Look at that. It works as expected. So this is one option.
2 .Option 2 is basically to disable the option 'Prevent saving changes that require a table recreation' in SQL Server.

All we need to do is disable that option. To do that, within the tool, select options and then expand designers. Under that, navigate to Table and Database Designers and uncheck the checkbox 'Prevent saving changes that require table recreation'.

• Now you can change the column datatype from designer also. let’s try
  Right-click on table ->select designer
• and then let’s try to change the salary datatype to decimal.
• Let’s save the changes and see where it works.