How to change database table columns without dropping the table

How to change database table columns without dropping the table

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

Notice that this table has got Id DoctorName, gender, salary, and SpecializationId columns.And I have used the below  create table script to create that table.
And if you notice the salary column data type 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 which is going to list the total salaries of doctors grouped by gender. So we want the output to be like the below image.
1

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 we cannot use the salary column, with Sum() aggregate function. because it’s data type is nvarchar. So we need to change the data type of salary column from nvarchar to float,int or decimal.

2

And one way to do that within the object explorer. Right-click on the table TblDoctors click design and notice the data type of the salary column is nvarchar, let’s change it to Integer and let’s try to save it and see what’s going to happen.
2

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 has already got some data as well. So if we drop and recreate it, we are going to lose that data. So 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.  so 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.
3

2 .Option 2 is basically to disable option prevent saving changes that require a table recreation in SQL server.

All we need to do is disable that option. And to do that, within the tool select options and then expand designers and under that table and database designers and uncheck the checkbox, prevent saving changes that require table creation.

3

4

  • 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.
    4

Ashok Patel

I'm an electronic engineer working in a multinational company,having good experience on Electronics and electrical engineers design and oversee production of electronic equipment such as radios, televisions, computers, washing machines and telecommunication systems.I like to do RND and Research.I also have hands on experience graphic design software and in web designing having great command on ASP.NET, HTML5, JavaScript, T-SQL, JQuery.

Add comment

Your Header Sidebar area is currently empty. Hurry up and add some widgets.