Support us .Net Basics C# SQL ASP.NET Aarvi MVC Slides C# Programs Subscribe Download

Part 67 - Alter database table columns without dropping table

Suggested Videos
Part 64 - Replacing cursors using joins in sql server
Part 65 - List all tables in a sql server database using a query
Part 66 - Writing re-runnable sql server scripts

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.



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. The output should be as shown below.
Total salary of employees grouped 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.
Alter database table columns without dropping table

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
sql server 2008 database options

1 comment:

  1. Hello Sir,

    If there is a scenario where i want to change the data type for a particular column and also that column values associated with store procedure,functions etc need to change the data type

    For ex:StudentId has Int datatype initially then i want to change it to BigInt in all the places SPs,functions etc how can i handle it..pls help

    ReplyDelete

It would be great if you can help share these free resources