HomeОбразованиеRelated VideosMore From: kudvenkat

Alter database table columns without dropping table Part 67

215 ratings | 87719 views
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 http://csharp-video-tutorials.blogspot.com/2013/08/part-67-alter-database-table-columns.html Slides http://csharp-video-tutorials.blogspot.com/2013/09/part-67-alter-database-table-columns.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists 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
Html code for embedding videos on your blog
Text Comments (40)
Majid Siddiqui (1 month ago)
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.
Dizzy Topic (5 months ago)
Excellent video! Thank you!
Shubhrajit Basu (5 months ago)
Is it possible to change column data type in SQL server using cast or convert command?
Parthiban R (7 months ago)
Please add a video for using EXIST and NOT EXIST in Sql which will be helpful
MarioAxl (8 months ago)
thanks it is a good to know
Fakhr Aldeen (1 year ago)
Thank you a lot
Nordien1978 (1 year ago)
Thank you good video explanation. it did help out :)
Mohammed Salim (1 year ago)
thank so much this info was a life saver for me
Dhana vindhan (1 year ago)
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.
Abhijeet Tote (1 year ago)
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
ziiip (1 year ago)
RIP last slide sentence.
Krzysztof S (1 year ago)
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.
فاطمة (2 years ago)
Thank you a lot,really I was need this video , yesterday I do a big mistake losing work of month
kudvenkat (2 years ago)
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 https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd If you need DVDs or to download all the videos for offline viewing please visit http://www.pragimtech.com/order.aspx Slides and Text Version of the videos can be found on my blog http://csharp-video-tutorials.blogspot.com Tips to effectively use my youtube channel. https://www.youtube.com/watch?v=y780MwhY70s If you want to receive email alerts, when new videos are uploaded, please subscribe to my youtube channel. http://www.youtube.com/subscription_center?add_user=kudvenkat 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. Good Luck Venkat
Mourad Dardari (2 years ago)
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
Tina M (2 years ago)
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: 'Orders' table - Unable to modify table. Conversion failed when converting the nvarchar value '10248 ' to data type int. Please guide. Thanks.
Christopher Backen (3 years ago)
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.
aperxmim (3 years ago)
What if you do this instead Kudvenkat  SUM(Cast(Salary as int)). Will this work?
VIKESH POOJARY (2 years ago)
+aperxmim It will work.
eliseocar1 (4 years ago)
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
Sheehan Iqbal (4 years ago)
It didn'nt work for me Im not sure whey..
hellofriend2012 (4 years ago)
Hi Venkat, After watching your great tutorials I have lost my interest in reading book.......
rajesh kumar (5 years ago)
Also we can typcast dynamically using cast keyword such as, SELECT GENDER,SUM(CAST(SALARY AS INT)) AS TOTAL FROM TBLEMPLOYEE GROUP BY GENDER
Anurag Nayak (5 years ago)
I faced a lot of time this problem..... Now i got the solution :)
Anurag Nayak (5 years ago)
You are doing great work.....In our project now i am using these concepts after learning from here....
kudvenkat (5 years ago)
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.
Venetia Faber (5 years ago)
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.
Kiran Pedamkar (5 years ago)
what is bitmap index and b-tree indexes
Kiran Pedamkar (5 years ago)
ok sir thats rigth, and one more thing which i have to request you that, what is composite, alternate, candidate, and super keys...
Suresh Kumar (5 years ago)
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.
Vaibhao Tayade (5 years ago)
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 !!!
kudvenkat (5 years ago)
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.
Kiran Pedamkar (5 years ago)
And if i am storing string in id column like R01114181 then how to invoke recently inserted value of id column
Kiran Pedamkar (5 years ago)
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.
kudvenkat (5 years ago)
Hi Kiran, can you please rephrase your question. I am not sure I have understood it correctly.
Kiran Pedamkar (5 years ago)
sir how to invoke last generated id column, specially when we are not using identity specification on, on the id column...
Gultekin Hesenova (5 years ago)
Thanks for this video.
Derek Matthews (5 years ago)
Thanks for that!
Muhammad Afzal (5 years ago)
. my question is hand coding or cms use to develop e-commerce site please answers the questions
Saagar Soni (5 years ago)
thank u sir..

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.