Alter database table columns without dropping table Part 67 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this part 67 of sequel server tutorial
in this video we'll discuss altering
database table columns without dropping
the table let's understand this with an
example we'll be using this table TBL
employee for this demo notice that this
table has got ID name gender and salary
columns and I have used this create
table script to create that table TBL
employee and if you notice the salary
column and where care is the data type
and this is the sequel script to
populate it with some sample data if you
need the sequel script I will have it
available on my blog and let's say based
on this table we want to write a query
which is going to list the total
salaries of employees grouped by gender
so we want the output to be like this so
let's write a query for that so select
gender and we want sum of salary and
let's give it as an Maria's total from
table TBL employee and we want to group
the salaries by gender and keep in mind
the salary column data type is envy cap
and let's try to execute this and see
what's gonna happen look at the error
message it states operand the data type
and we're care is invalid for some
operator so basically this error message
is stating we cannot use the salary
column with some aggregate function
that's basically because it's data type
is n we're care so we need to change the
data type of this column from n we're
care to integer and one way to do that
within the object Explorer right click
on the table TBL employee click design
and notice the data type of the salary
column it's n we're care let's change it
to integer and let's try to save it and
see what's going to happen look at that
I get a warning saving changes is not
permitted basically this is telling we
need to drop and recreate the table and
if you remember this table has already
got some data as well so if we drop and
recreate it we are going to lose that
data so how do we get rid of this
warning mass
okay basically there are two ways option
one use a sequel query to alter the
column okay so instead of using the
sequel server designer we can simply use
a sequel query so let's write the sequel
query alter table TBL employee and
within that table we want to alter the
column salary and we want to change the
data type to integer so let's execute
that and let's now run this query and
see if it works as expected look at that
it works as expected so this is one
option and the second option is
basically to disable this option within
Cygnus server so prevent saving changes
that require a table recreation okay
this option is on by default within
sequel server all we need to do is
disable that option and to do that go
back to sequel server within tools
select options and then expand designers
and under that table and database
designers and uncheck this checkbox
prevent saving changes that require
table 3 creation
click OK now let's try to design the
table now at the moment salary data type
is integer maybe let's change it to
something like maybe small integer let's
save the changes and see where it works
look at that it gives a warning but
that's fine once I click yes look at
what's gonna happen the table the
changes are saved and as you might
expect when we execute this query we get
the same output that's it for today
thank you for listening we great day