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
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.
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.
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.
You are very welcome and Thank you very much for taking time to give feedback. This means a lot. I am very glad you found the videos useful.
I have organised all the Dot Net & SQL Server videos in to playlists, which could be useful to you
If you need DVDs or to download all the videos for offline viewing please visit
Slides and Text Version of the videos can be found on my blog
Tips to effectively use my youtube channel.
If you want to receive email alerts, when new videos are uploaded, please subscribe to my youtube channel.
If you like these videos, please click on the THUMBS UP button below the video.
May I ask you for a favor. I want these tutorials to be helpful for as many people as possible. Please share the link with your friends and family who you think would also benefit from them.
Hi ..I am unable to modify using Alter statement:
ALTER TABLE ORDERS
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:
- Unable to modify table.
Conversion failed when converting the nvarchar value '10248 ' to data type int.
Please guide. Thanks.
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.
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.
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 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.
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.
Cute girls groups. This hot chick Monica was willing to bathe and get suntanned on this sunny summer day that’s why she invited three guys to make her a company on the river coast. Could she ever imagine that these frisky guys would want something more than just sitting and talking about nothing? Well, she very quickly found out that when the young boys see a charming chick in tiny bikini everything they can think about is hard fuck! They started pounding her in the river and then moved to a more comfortable place where all three in turns stuck their peckers in her wonderful mouth and then bent her over and diddled in pussy and ass without any mercy trying to reach the peek of pleasure and cover the skin with loads of semen.
Cute girls are made to be fucked)) And the cuter they are, the better and dirtier fuck session can be expected. HardFuckTales can offer you something really out of the ordinary – handsome beefy guys stuff their rock hard dicks in the deep wet mouths then pulsating pussies and tight asses of hot spoiled girls in public places which makes the action even more exciting. Not to be missed!
Categories:One On OneBig BoobsInterracial18+ TeenAsianFacial Cumshot Latina Swallow Tattoo Brunette Blonde Bubble Butt
Overview:Now that theyre finally out of Mom and Dads house, they can fuck whoever they wanna fuck, all day long, and thats just what they do! Watch these naughty coeds skip class and show some ass, among other things as they suck and fuck their way through their higher education years