Updatable views in sql server Part 40 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

hello welcome to presume technologies I
am venket this is part 40 of sequel
server in the session we learn about
updatable views before continuing with
the session I strongly recommend to
watch part 39 of this video series so
what are updatable views let's
understand that with an example I have
employees table here which has got ID
name salary gender and department ID
columns usually salary is confidential
information and we don't want employees
to be looking at other employees salary
details okay but then there is a
requirement where a single user needs to
low employee details in that case if you
grant the user access to the table he
will be able to see all rows and columns
including salary and we don't want that
so to avoid that we can actually create
a view which selects all the columns
except the salary column and that's what
we are exactly doing here we are
creating a view called employee
employees data except salary and if you
look at the select statement it is
selecting all the columns except the
salary column okay and then when you
select data from that view look at the
result we get all the columns except the
salary column so let's go ahead and
create this view on this employees table
let's flip back to sequel server
management studio so here is the view
view employees data except salary and if
you look at the select statement we are
selecting all the columns except salary
so let's create this view command
completed successfully so we created the
view refresh the views folder and we
should see the view that we have just
created
employees data except salary now to
select data from this view we issue the
Select statement the same way as we
would for a table so select star from
view name okay
so let's execute this and if you look at
the view itself look at this we get the
data from the view now if you look at
the view itself does it really store any
data and no a view if you look at this
view it is only a select statement a
stored select statement so it's a
virtual table it's not a real table
so when you execute this query the view
actually gets its data from TBL employee
table so here for this view TBL employee
is the underlying base table okay now
when you issue select star from view
name that view is actually going to get
its data from the underlying base table
because the view itself doesn't store
any data all right so since you are able
to issue a select statement against a
view which in turn will get its data
from the underlying base table can you
update a view which will update the
underlying base table absolutely that's
definitely possible so it's it's
possible to update a view to insert data
into your view and to delete data from a
view which in turn will actually do
those respective operations on the
underlying base table in this case TBL
employee so when you update or when you
insert into this view it will actually
update the underlying base table all
right so let's look at that in action
okay so let's select the data from the
view so we got all this data here now
let's update this view so I'm updating
the view update view and we are saying
set name is equal to my key pair ID is
equal to two so for record two we are
changing the name to my key currently it
is Mike and we are updating it to my key
so let's update this command one row
affected now let's select the data from
the view Mike should have been changed
to my key now let's select data from the
employee table and see if the underlying
base table is updated so if you look at
the underlying base table TBL employee
table itself name is updated to my key
so it's possible to update the base
table using views in this case we call
the view as updatable view and in sequel
server views are updatable okay now we
can also delete and insert data from a
view which in turn will obviously delete
an insert data from the underlying
table okay so so we have all the records
here from the view let's try to dela
delete record the tidy is equal to two
in this case my keys record so delete
from view where ID is equal to two so
let's execute that one row affected
let's select the data back from view and
see if it's really deleted so there's no
more you know my case record their
record with ID is equal to two is
deleted along the same lines we can also
insert data into the view so let's
insert data into the view insert into
this view values and we are passing in
the values press f1 row affected now
let's select the data back from the view
and we should see you know Mike is
record back in there alright so views
are updatable now if you look at the
view that we are creating here this view
is based on one base table in this case
TBL employee but in reality it is
possible you can create a view based on
multiple tables okay so if you look at
the view here I am creating a view I
mean we have TBL employee and TBL
department table on both of these tables
I am creating a view which returns to me
ID name salary gender and department
name so the first four columns ID name
salary and gender are coming from TBL
employees table and department name is
coming from TBL department table okay so
we are creating this view which joins
employee table with the department ok
now if you have a view that's based on
multiple tables and when you update that
view what happens that's what we will be
looking at now okay
so let's first create this view view
employee details by department okay and
we have spoken about this in a great
detail in the previous session so if
you're new to creating views I would
strongly recommend to check path 39 of
this video series so let's go ahead and
create this view now so create view
employee details by Department so we are
selecting ID names every gender and
Department name columns
from TBL employee table joining that
with TBL department on department ID
column which is common between both the
tables so let's create this view now
command completed successfully refresh
the views folder and we should see the
view that we have just created all right
okay now let's select the data from the
view just to make sure we get data as
expected so we get ID names Hilary
gender and Department name columns all
right so now if you look at John's
record John is currently within HR
department now let's try to update his
department to IP from HR but before we
do that note who else is within the HR
department along with Beto John Ben is
also within HR department so they
currently two employees John and Ben
within HR department okay now when I
issue this update statement we are
actually updating John's department to
be IT department from HR department so
only this should be converted to IT Ben
should still remain in the HR department
so let's update this and see what's
going to happen one row affected now
let's select the data back from the view
ok John's department is set to IT which
is good but look at Ben's department you
know we didn't expect this we expected
this to be HR but then our update
statement didn't update correctly
something has gone wrong so let's
analyze why you know why is Ben's Ben's
department name is also changed okay now
obviously when we issue no matter
whether your view is based on a single
base table or multiple base tables when
you update the view it's going to update
the underlying base tables why because
the view itself doesn't store any data
by default we can change that default
behavior using materialized views which
we'll be talking about in a later
session but by default the view doesn't
really store any data so when you issue
a select insert update or delete
statement these statements are executed
against the underlying base tables okay
so here
when you updated this view it is
actually behind the scenes underlying
the updating the underlying base tables
TBL employee and TBL department so let's
select data from those tables and see
what's actually happening so here we
updated John's a department to be HR I
mean to ID from HR but then that also
changed Ben's Department to IT let's see
what actually happened if you look at
this if you look at the department's
data and look at this
John's department ID is three Ben's
department ID is three now when you
update it when you issue this update
statement when you say update this view
set Department name is equal to I T
where name is equal to John so for John
you are saying set department name is
equal to ID that means this department
ID for this John's record should be
changed from 3 to 1 ok but in fact what
has actually happened is when you update
this query the view incorrectly updated
the department name column here now the
view is updating look at this for three
which was HR before it converted that to
IT as a result of which all the
employees who have three is showing I
Tina so been department ID was three so
Ben is showing it as IT ideally we would
we should have I mean when you updated
this view it should have updated the
department ID to one from three so
that's exactly it's what's happening
here so since it's since it updated the
department name itself here and whoever
is three it is showing ID for them so
here John is three been department ID is
three and three IT before this before
our update statement it was actually HR
that's why we have this incorrect update
ok so the conclusion if a view is based
on multiple tables and if you update
that view
may not update the underlying base
tables correctly and to do that
correctly
we can make use of instead of triggers
we haven't spoken about triggers yet
we'll be talking about triggers and how
to use those instead of triggers to
update a view correctly that is based on
multiple tables in a later video session
on this slide you can find resources for
asp.net c-sharp and sequel server
interview questions that's it for today
thank you for listening have a great day