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.


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