Views in sql server Part 39 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

hello welcome to play genome
technologies I am venket this is path 39
of signal server in this session we'll
learn about what a view is and the
advantages of using views so what is a
view a view is nothing more than a saved
sequel query a view can also be
considered as a virtual table let's look
at an example which will make this
definition clear on this slide you can
see the department's table which has got
department ID and Department name
columns and this is employee table which
has got ID name salary gender and
department ID columns now I want you to
write a query which can produce the
output that you can see on the right
hand side ok so the ID name salary and
gender comes from the employees table
and department name comes from the
department table so obviously to produce
this output we will have to join
employee's table with the department's
table and we have seen how to do that in
part 12 of this video series we have
spoken about joins extensively in that
part ok so here we have a join query
which returns I renamed salary gender
and department name from these two
tables by joining them ok so now how to
create a view ok to create a view we use
create view statement I mean if we want
to create a table we use Create table
and along the same lines we want to
create a procedure we use create
procedure statement so similarly to
create a view create view and view name
ok and if you look at the name of the
view here you're getting employees by
Department ok so in the name of the view
we have prefixed that with V W standing
for view ok so just by looking at the
name of s equals our object you know if
you follow the right naming conventions
you can tell what type of object it is
for example if it's a table we prefix
that usually with TBL if it's a stored
procedure we prefix that with sp ok so
along the same lines if it's a view
prefix that that V W so just by looking
at the name of an
object you can tell what type of object
it is okay so create view view name as
and then after as you specify your
select query okay so what is this view
doing this view is returning employees
by Department and that's the reason why
we have written this joint query first
okay because you know when we look at
views we don't want to get into the
complexity of writing a joins query okay
so that's why we have written that query
and all we have done here is create view
view name as we are just putting our
Select query there okay
so let's create this view using sequence
of our management studio okay so we have
got these two tables that we have just
seen the employees and departments table
and obviously we are you know if I
execute this query look at that I get
all the rows now this is just a sequel
query and it is not stored anywhere okay
so let me go ahead and create a view so
to create a view we use the create
statement so create view and view name
let's call this employees by department
okay as and then your Select query
that's about it so let's select this and
execute so command complete it
successfully so this view is now created
in this equal server
so once the view is created how do you
see that view so to view the to see the
view that is just created go into the
database which is sample database
because we created this view in the
sample database so selects expand sample
database and look in views folder so
when I expand that I don't see anything
so let's refresh that look at this we
can see the view view employees by
department okay so now we have created
this view so if you look at the view
itself does it contain anything it it
doesn't really contain anything it is
just a select query okay so now let's
say I want to select data from this view
how do I do that you can treat this view
just like a table so if I want to sell
data from that I can just say select
star from that view so what's overview
VW employees by department based on f5
so I am treating this view like a table
so when you select data from the CPU in
the sense when you execute the Select
query what actually happens is the
database engine knows that ok this view
is actually getting its data from the
stabile employee and TBL department
tables so for this view these two tables
are the underlying tables of base tables
okay so when we execute the select star
from view employees by department sequel
server engine actually executes the
Select query on these two base tables
and written as the data from those
tables okay so a view in fact doesn't
store any data it's it's but here we are
treating that view like a table that's
why if view is called as a virtual table
but in fact when you look at the
definition of the view it doesn't store
any data it is only a saved select query
the reason why we say it as a saved
select query is because when I say SP
underscore help text and then you know
the name of the view you can actually
look at the definition of that view okay
now we can use SP and this Co help text
with a stored procedure with a function
you know similarly along the same lines
you can use that with view as well to
get the view definition so what is
stored in the database only the Select
query so a view is nothing more than a
stored query or you can treat that as a
virtual table and what does the
definition say a view is nothing more
than a saved sequel query a view can
also be considered as a virtual table so
creating a view itself is pretty simple
now let's turn our attention to what are
the advantages of using views of where
do we actually use these views now views
can be used to reduce the complexity of
the database schema now we have just
done that if you look at this
you know let's
we have some non IT users within our
organization who interact with our
database so non IT users you know joins
us a little complex topic for them okay
so writing it joins queries not that
easy for non IT users but then they want
to join maybe employees Department
gender you know all the other tables
maybe they want to join like five to six
tables so writing a joint query is not
simple for non IT users okay so in that
case what you can actually do is write a
view which joins all those tables and
then give access to that user to the
view and then that user can treat that
as a single table that view as a single
table and it is easy for him to query
that table and they can simply use the
where clause and filter whatever data
they want okay
so obviously views can be used as a
mechanism to simplify the database
schema for non IT users okay and we have
looked at an example of how to join two
tables but we can join more than two
tables okay so what's the other
advantages of using views views can also
be used as a mechanism to implement row
and column level security okay let's
look at an example of that so how can
views be used to implement row and
column level security now if you look at
the view that we have at the minute this
view is basically returning all
employees within my organization now let
us say there is an IT department manager
and I want to provide him access only to
see IT department employees in that case
if I grant him access to the table stem
cells what's going to happen he can
issue a select query from those tables
and he can see all the rows and all the
columns so I don't want to do that I
want to allow him access only to his
department employees so how can I
achieve that in that case create a view
and within the view retrieve only IT
department employees and then to that
employee give
access to that view let's do that so we
already have this view so let's copy
this so instead of granting him access
to the underlying base tables what I'm
going to do is I'm going to create this
view view maybe we'll call this IT
employees okay
and what we want to do we don't want to
written if you look at this select quit
if I just execute the Select query it's
going to return all the employees but we
don't want that we want to filter the
employees by department so what you can
actually do is you can say where TBL
department dot department name is equal
to YT so when we create this view and
then once we grant access to that
employee to this view he can only see IT
employees and nobody else
so select star from this view he should
only see the ID employees now so only
the IT employees okay so we have just
implemented row-level security along the
same lines we can also implement column
level security for example in any
organization salary is confidential
information and we don't want everybody
to see the salaries of other people in
that case you know we can actually
create a view which hides salary column
and then retrieves all the other columns
let's quickly do that so obviously to do
that we can create you know F view which
returns non confidential information so
let's give it some meaningful name view
non confidential data and we don't want
this ver close because we don't want to
implement rule level security we want to
implement the column level security so
from the Select list I am going to knock
off the salary column and let's create
this view now so what's going to happen
here
you're only going to get the all the
columns except the salary column so
select star from that view look at that
we get every column except the salary
column since the employee does not have
access to the underlying base tables
there's no way he can get access to that
salary column so views can be used as a
mechanism to implement true level and
column level security and views can also
be used to present only aggregated data
for example let's say you want to
prevent users the end users from looking
at the detailed data you want to provide
them only with aggregate data so how can
we do that again using views okay so in
your view differently don't grant them
access to the underlying base tables
which will act I mean if you grant them
access to the underlying base because
they can see everything so instead of
that we can create a view which
aggregates data and then return them
back and obviously to the two that do
the add create view let's call this view
summarized data as what do we want we
want let's say for example if you look
at this data I want the total number of
employees by department for example in
IT department how many employees are
there in HR department or how many
employees are there so we want total
number of employees by Department this
summarized data and obviously to do that
we have to use group by clause and we
have seen how to use the group by clause
in the previous sessions of this video
series so we want the department name
itself so select the e PT name and then
we want the count of employees so
viewing the count we use the count
aggregate function so count of ID and
let's say total employees
from the tables which are TBL employee
and we want to join that obviously with
TBL department on TBL employee dot
department ID is equal to act let's copy
that TBL department dot department ID
and we want to use the glue by clause to
group by department name let's create
this view fi command completed
successfully now let's select the data
from this view so select from view
summarized data so now we get the
aggregated data department name and
total number of employees so views can
also be used to present aggregate data
only and hide detailed data and
obviously to modify a view we can use
all the view statement now to modify any
object in sequel server we use I mean
for example alter table table name
similarly alter procedure procedure name
along the same lines if you want to
alter a view alter view view name and
similarly if you want to drop a view
you'll say drop view and view name on
this slide you can find resources for a
speed or net c-sharp and sequencing
server interview questions that's it for
today thank you for listening have a
great day