Alter database table columns without dropping table Part 67

Công Nghệ

In this video, we will discuss, altering a database table column without having the need to drop the table. Let’s understand this with an example.

Text version of the video


All SQL Server Text Articles

All SQL Server Slides

All Dot Net and SQL Server Tutorials in English

All Dot Net and SQL Server Tutorials in Arabic

We will be using table tblEmployee for this demo. Use the sql script below, to create and populate this table with some sample data.
Create table tblEmployee
ID int primary key identity,
Name nvarchar(50),
Gender nvarchar(50),
Salary nvarchar(50)

Insert into tblEmployee values(‘Sara Nani’,’Female’,’4500′)
Insert into tblEmployee values(‘James Histo’,’Male’,’5300′)
Insert into tblEmployee values(‘Mary Jane’,’Female’,’6200′)
Insert into tblEmployee values(‘Paul Sensit’,’Male’,’4200′)
Insert into tblEmployee values(‘Mike Jen’,’Male’,’5500′)

The requirement is to group the salaries by gender.

To achieve this we would write a sql query using GROUP BY as shown below.
Select Gender, Sum(Salary) as Total
from tblEmployee
Group by Gender

When you execute this query, we will get an error – Operand data type nvarchar is invalid for sum operator. This is because, when we created tblEmployee table, the “Salary” column was created using nvarchar datatype. SQL server Sum() aggregate function can only be applied on numeric columns. So, let’s try to modify “Salary” column to use int datatype. Let’s do it using the designer.
1. Right click on “tblEmployee” table in “Object Explorer” window, and select “Design”
2. Change the datatype from nvarchar(50) to int
3. Save the table

At this point, you will get an error message – Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.

So, the obvious next question is, how to alter the database table definition without the need to drop, re-create and again populate the table with data?
There are 2 options

Option 1: Use a sql query to alter the column as shown below.
Alter table tblEmployee
Alter column Salary int

Option 2: Disable “Prevent saving changes that require table re-creation” option in sql server 2008
1. Open Microsoft SQL Server Management Studio 2008
2. Click Tools, select Options
3. Expand Designers, and select “Table and Database Designers”
4. On the right hand side window, uncheck, Prevent saving changes that require table re-creation
5. Click OK


Xem thêm bài viết khác:

22 thoughts on “Alter database table columns without dropping table Part 67

  1. Hi, can you please help me to know that how to disable/enable updating any particular column in a SQL Table. Is there any query or option that the specific column cannot be update while doing any transaction unless it is not enable/permissible.

  2. Hello sir, I had a scenario in my interview to solve which is ( I have a application which generates pay slip like Gross,ESI, PF,DA,HRA and Net amount) Now, I want to add a column to this existing table like (Special Allowance) without changing the application structure. How do I do that? My answer: I told them I will alter the table with new column. But they said it will change the application structure. Please explain this concept.

  3. Hi Venkat,

    I want to say Thank You Much !!
    Because of you I have got start in IT industries. I belongs to very small town where there is no training available for any tech. Also no financial support to move to metro city and learn those. Your you-tube video has given me platform to learn and start my carrier.
    Currently i am working as consultant in one of the good company. Thank you so much!!
    Let me know if i can help you in any ways that i can.


  4. Short video, but very useful. I needed this video during some earlier video sessions when I had some tables and I needed to recreate them.

  5. thanks you so much for this tuto without you i had a big problème because i forget to add identity in all my table thanks again

  6. Hi ..I am unable to modify using Alter statement:
    ALTER COLUMN CUSTOMERID INT – It gives errorfor first value saying :
    Msg 245, Level 16, State 1, Line 6
    Conversion failed when converting the nvarchar value '90 ' to data type int. 90 is first value in this variable. Please guide.

    In fact, using another method also giving same error as below: It saves but does not modify: Error message:
    'Orders' table
    – Unable to modify table.
    Conversion failed when converting the nvarchar value '10248 ' to data type int.

    Please guide. Thanks.

  7. Hello, I could use a bit of help with finding data types. I am using Microsoft SQL 2014 Management Studio 12.0.2000.8. I have the AdventureWorksLT database loaded but can't get the data type to display. From what I found you use the table designer to view the table properties. But when I right click on the table I don't have that option.

Leave a Reply

Your email address will not be published. Required fields are marked *