asp.net core docker 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
http://csharp-video-tutorials.blogspo...

Slides
http://csharp-video-tutorials.blogspo...

All SQL Server Text Articles
http://csharp-video-tutorials.blogspo...

All SQL Server Slides
http://csharp-video-tutorials.blogspo...

All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenka...

All Dot Net and SQL Server Tutorials in Arabic
https://www.youtube.com/c/KudvenkatAr...

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

asp.net core docker Alter database table columns without dropping table Part 67
283 Likes	283 Dislikes
103,448 views views	524K followers
Education	Upload TimePublished on 12 Aug 2013

Related keywords

wcf vs web api,ado.net core,sql server management studio,webkinz,webadvisor,craigslist nj,wcf one piece,asp.net core 3,ajax players,weber grill parts,webtoon,tutorials by hugo,csharp corner,mvc design pattern,asp.net machine account,servers for minecraft,asp.net core dependency injection,ado.net tutorial,database normalization,services angular,database management system,ajax ontario,columns and rows,asp.net mvc tutorial,asp.net cos'è,csharp assembly,columns ayala for rent,database types,tutorialspoint python,sql join,services briefcase,asp.net core web api,ajax jquery,wccftech,craigslist ny,asp.net zero,csharp foreach,server memes,sql date format,database online,services online,chase,serverless architecture,server resume,wcf c#,server books,tutorialspoint javascript,mvcc connect,ado.net mysql,database.ensurecreated(),services technologies gps,ajax deadpool,server jobs,cvs,columns ayala,website,mvc tutorial,costco hours,wcf service application,tutorialspoint spring,serverless,wcf soap,columns to rows excel,columns css,wcf cat,wcf test client,cool math games,columns to text excel,columns greek,services & training hse colombia sas,servicestack,citibank,asp.net core identity,sql union,ajax parking,sql database,asp.net core logging,columns meaning,mvconnect,asp.net guida,cunyfirst,wcf nba,csharp download,wcfi foundation,csharp online,wcf authentication,tutorials near me,http://asp.net,server 2019,ado.net descargar,web of dreams,chernobyl,serverminer,ajax cleaner,ado.net visual studio 2019,webassign,database diagram online,ado.net vs entity framework,ado.net visual studio 2017,csharp list,sql like,asp.net mvc,asp.net core tutorial,sqlite,wcfm,ajax roster,columns buendia,mvc architecture,http://ado.net,asp.net core mvc,ajax soccer,server hosting,database schema online,wcfi,columns vs rows,ajax dish soap,columns in excel,capital one,server rack,tutorialspoint html,csharp interface,database disk image is malformed,craigslist,webroot,tutorialspoint reactjs,ajax request,wcf dragon ball,asp.net core 2.2,database administrator,tutorialspoint python 3,sql developer,webster,services transmission company sas,sql group by,asp.net core signalr,services manager,database index,mvc framework,ajax paving,mvc near me,database migration,database schema,tutorialspoint spring boot,database developer,mvc map,csharp online compiler,columns in newspaper,asp.net download,sql between,ado.net c# pdf,wcf 2019 nba,services tag dell,csharp switch,ado.net ventajas y desventajas,csharpstar,wcf tutorial,columns legazpi village,tutorialspoint,ajax meaning,csharp-video-tutorials.blogspot,tutorials dojo,csharp string format,central park 5,ado.net c#,asp.net core github,server status,ajax fc,server jobs nyc,asp.net core swagger,sql formatter,credit karma,services group,columns condo makati,server error in '/' application,services windows,asp.net core 3.0,sql injection,wcf ria services,tutorialspoint c#,calculator,ado.net entity data model,sql insert,tutorialspoint tableau,services google play apk,sqlyog,database first,columns and beams,asp.net core 3 release date,sql server,server job description,tutorials by a,servicenow,webcam,mvc hours,webmd symptom,csharp array,csharp enum,ajax call,asp.net core 2. guida completa per lo sviluppatore,asp.net core,server pro,server status ffxiv,database is,webcrims,cheap flights,asp.net core hosting,services sas,database management systems,tutorialspoint java,tutorialspoint java compiler,webmd,csharp to json,college football,ado.net dataset,csharp dictionary,columns makati for rent,cnn,website builder,columns in tagalog,tutorialspoint sql,asp.net web api,server side rendering,weber grills,sql server 2017,mvc nj,tutorialspoint spark,ado.net oracle,asp.net core download,csharp to vb.net,webster bank,webstaurant,columns of bertin,tutorialsystems,ajax post,services fms publish announcement,services.msc no abre,ajax jersey,csharp operator,asp.net core razor pages,server duties,asp.net core environment variables,columns in word,csharp random,database design,century 21,services consultores,services consulting,database diagram,mvcsd,mvcsc,services.msc,ado.net pdf,asp.net core configuration,ajax marvel,sql update,asp.net tutorial,mvc medical,ado.net entity data model visual studio 2019,wcfs international curriculum,mvc2,ado.net entity data model visual studio 2017,columns makati,chase online,wcf api,costco,database это,server jobs near me,webex,sql meaning,sql tutorial,sql commands,ado.net entity framework,ajax tavern,tutorialsteacher,databases list,ajax javascript,services desk,ajax greek,csharp tutorial,mvc pattern,ado.net sql server,ado.net connection,asp.net identity,mvcu,asp.net core middleware,wcf web service,mvc webadvisor,web store,mvcc,webmail,mvci,mvctc,