Alter database table columns without dropping table Part 67

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

Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.

Slides

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

[Csharp-video-tutorialsBlogspot]

Sure, will record and upload videos on the concepts you mentioned. In the description of this video, I have included the link for ASP .NET, C#, and SQL Server playlists. All the videos are arranged in logical sequence in these playlists, which could be useful to you. Please share the link with your friends who you think would also benefit from them. If you like these videos, please click on the THUMBS UP button below the video.

[krzysztofs]

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.

[vaiking]

Thanks for SQL Server tutorial videos....
I have gone through all of your videos, and I m happy to say, It was Great learning from you sir. Keep it up! and All the best !!!

[abhijeettote]

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.

Thanks,
Abhijeet

[syxbuqq]

Thank you a lot, really I was need this video , yesterday I do a big mistake losing work of month

[venetiaf]

Hi Venkat, The SQL Server videos are very informative and easy to understand, and it was a great learning experience. I request you to add a few more topics such as Creating Jobs, Query Optimization, XML, Isolation Levels, Entity relationships, SSRS, SSIS, SSAS etc. It would really help me and others beginners. Keep up the good work! and i've subscribed :)

Thanks.

[Csharp-video-tutorialsBlogspot]

Hey Kiran, if it is not an identity column, then you will have to supply the value for the ID column. This means you already have the ID value of the row that you are about to insert. Please let me know if this answers your question.

[salim]

thank so much this info was a life saver for me

[Nordien]

Thank you good video explanation. it did help out :)

[mouraddardari]

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

[SureshKumar-gmgz]

Thanks for the SQL Server videos.

The videos provided by you are excellent and it is very clear that a beginner can easily become an expert in SQL Server.

Could you please post videos on SSIS, SSRS and SSAS.

[dhanavindhan]

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.

[reyrajesh]

Also we can typcast dynamically using cast keyword such as,

SELECT GENDER, SUM(CAST(SALARY AS INT)) AS TOTAL FROM TBLEMPLOYEE GROUP BY GENDER

[kiranpedamkar]

ok sir thats rigth, and one more thing which i have to request you that, what is composite, alternate, candidate, and super keys...

[Csharp-video-tutorialsBlogspot]

Hi Kiran, can you please rephrase your question. I am not sure I have understood it correctly.

[kiranpedamkar]

my question is that, if i am not using identity specification on, on id column then how to invoke last inserted or generated row value of id column.

[bucket]

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.

[ParthibanRNellukkuIraithaneer]

Please add a video for using EXIST and NOT EXIST in Sql which will be helpful

[kiranpedamkar]

sir how to invoke last generated id column, specially when we are not using identity specification on, on the id column...

[eliseocar]

i need your help. Is there any way to swap two columns between one table?
and how can i add a new column between two columns?
thank you very much