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,
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
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