YT YouTube2Text

YouTube2Text → Transcripts → Views in sql server Part 39

Views in sql server Part 39 — Transcript

by kudvenkat · 2,283 words · 331 segments · language en · Watch on YouTube

Open in transcriber Summarize with AI Download SRT/VTT

Full transcript

0:00hello welcome to preim Technologies I'm
0:02wet this is part 39 of SQL server in
0:06this session we'll learn about what a
0:08view is and the advantages of using
0:10views so what is a view a view is
0:13nothing more than a saved SQL query a
0:16view can also be considered as a virtual
0:18table let's look at an example which
0:21will make this definition clear on this
0:24slide you can see the Departments table
0:26which has got Department ID and
0:27Department name columns and this this
0:30employee table which has got ID name
0:32salary gender and Department ID columns
0:35now I want you to write a query which
0:38can produce the output that you can see
0:39on the right hand side okay so the ID
0:42name salary and gender comes from the
0:44employees table and Department name
0:47comes from the Department table so
0:49obviously to produce this output we will
0:52have to join employees table with the
0:54Department's table and we have seen how
0:56to do that in part 12 of this video
0:59series we have spoken about joins
1:01extensively in that part okay so here we
1:05have a join
1:06query which returns ID name salary
1:09gender and Department name from these
1:12two tables by joining them okay so now
1:15how to create a view okay to create a
1:18view we use create view statement I mean
1:22if we want to create a table we use
1:24create table and along the same lines if
1:26we want to create a procedure we use
1:29create procedure procedure statement so
1:31similarly to create a view create View
1:34and view name okay and if you look at
1:36the name of the view here we getting
1:38employees by Department okay
1:42so in the name of the view we have
1:45prefix that with VW standing for view
1:49okay so just by looking at the name of a
1:51SQL Server object you know if you follow
1:55the right naming conventions you can
1:56tell what type of object it is for
1:58example if it's a table
2:00we prefix that usually with TBL if it's
2:03a stored procedure we prefix that with
2:05SP okay so along the same lines if it's
2:08a view prefix that with VW so just by
2:11looking at the name of an object you can
2:13tell what type of object it is okay so
2:16create view view name as and then after
2:22as you specify your select
2:25query okay so what is this view doing
2:28this view is returning employees by
2:31department and that's the reason why we
2:33have written this join query first okay
2:36because you know when we look at views
2:38we don't want to get into the complexity
2:40of writing a joins query okay so that's
2:43why we have written that query and all
2:45we have done here is create view view
2:47name as we are just putting our select
2:49query there okay so let's create this
2:52View using SQL Server management Studio
2:55okay so we have got these two tables
2:57that we have just seen the employees and
2:59Department's table and obviously we are
3:02you know if I execute this
3:05query look at that I get all the rows
3:08now this is just a SQL query and it is
3:10not stored anywhere okay so let me go
3:14ahead and create a view so to create a
3:16view we use the create statement so
3:18create View and view name let's call
3:21this
3:23employees by
3:27Department okay as and then your select
3:30query that's about it so let's select
3:32this and execute so command completed
3:36successfully so this view is now created
3:39in the SQL Server so once the view is
3:41created how do you see that view so to
3:44view the to see the view that is just
3:46created going into the database which is
3:49sample database because we created this
3:51view in the sample database so select
3:53expand sample database and look in views
3:56folder so when I expand that I don't say
3:58anything so let's refresh that look at
4:01this we can see the view view employees
4:05by Department okay so now we have
4:08created this view so if you look at the
4:10view itself does it contain anything it
4:13it doesn't really contain anything it is
4:15just a select query okay so now let's
4:19say I want to select data from this view
4:21how do I do that you can treat this view
4:24just like a table so if I want to select
4:27data from that I can just say select
4:30star
4:31from that view so what's our a view VW
4:35employees by
4:37Department paste that F5 so I am
4:41treating this view like a table so when
4:43you select data from this view in the
4:45sense when you execute the select query
4:47what actually happens is the database
4:49engine knows that okay this view is
4:52actually getting its data from this TBL
4:55employee and TBL Department tables so
4:57for this view these two tables are the
5:00underlying tables or base tables okay so
5:04when we execute the select star from
5:05view employees by Department SQL Server
5:08engine actually execute this select
5:10query on these two base tables and
5:13return us the data from those tables
5:16okay so a view in fact doesn't store any
5:20data it's it's but here we're treating
5:23that view like a table that's why a view
5:26is called as a virtual table but in fact
5:28when you look at the definition of the
5:30view it doesn't store any data it is
5:32only a saved select query the reason why
5:36we say it as a saved select query is
5:39because when
5:41I say Spore help text and then you know
5:46the name of the view you can actually
5:48look at the definition of that
5:52view okay now we can use Spore help text
5:55with a St procedure with a function you
5:57know similarly along the same lines you
5:59can can use that with view as well to
6:01get the view definition so what is
6:03stored in the database only the select
6:05query so a view is nothing more than a
6:08stored query or you can treat that as a
6:12virtual table and what does the
6:14definition say a view is nothing more
6:16than a saved SQL query a view can also
6:18be considered as a virtual table so
6:21creating a view itself is pretty
6:23simple now let's turn our attention to
6:27what are the advantages of using views
6:29of way do we actually use these views
6:31now views can be used to reduce the
6:34complexity of the database schema now we
6:36have just done that if you look at this
6:39you know let's say we have some non it
6:42users within our organization who
6:44interact with our database so non it
6:46users you know joints is a little
6:49complex topic for them okay so writing a
6:52joints query is not that easy for non it
6:55users but then they want to join maybe
6:59employees Department gender you know all
7:01the other tables maybe they want to join
7:03like five to six tables so writing a
7:05join query is not simple for non it
7:09users okay so in that case what you can
7:12actually do is write a view which joins
7:16all those tables and then give access to
7:19that user to The View and then that user
7:22can treat that as a single
7:25table that view as a single table and it
7:27is easy for him to query that table and
7:30they can simply use the we clause and
7:33filter whatever data they
7:35want okay so obviously views can be used
7:39as a mechanism to simplify the database
7:42schema for non-it
7:44users okay and we have looked at an
7:47example of how to join two tables but we
7:49can join more than two
7:52tables okay so what's the other
7:54advantages of using views views can also
7:57be used as a mechanism to implement row
7:59and column level security okay let's
8:01look at an example of that so how can
8:04views be used to implement Ru and column
8:06level security Now if you look at the
8:08view that we have at the minute this
8:10view is basically rning all employees
8:13within my organization now let us say
8:16there is an IT department manager and I
8:19want to provide him access only to see
8:23it Department employees in that case if
8:26I grant him access to the tables
8:28themselves what's going to happen he can
8:30issue a select query from those tables
8:33and he can see all the rows and all the
8:35columns so I don't want to do that I
8:37want to allow him access only to his
8:40Department employees so how can I
8:42achieve that in that case create a view
8:46and within the view retrieve only it
8:49Department employees and then to that
8:52employee give access to that view let's
8:55do that so we already have this view so
8:58let's call copy this so instead of
9:01granting him access to the underlying
9:05base tables what I'm going to do is I'm
9:07going to create this view view maybe
9:10we'll call this it
9:16employees okay and what we want to do we
9:18don't want to return return if you look
9:20at this select query if I just execute
9:22the select query it's going to return
9:23all the employees but we don't want that
9:25we want to filter the employees by
9:27department so what you can actually do
9:30is you can say
9:32where um TBL
9:35Department Department
9:37name is equal to
9:42it so when we create this
9:45View and then once we Grant access to
9:48that employee to this view he can only
9:51see it employees and nobody else so
9:54select star from um this View
10:02he should only see the it employees now
10:05so only the it
10:06employees okay so we have just
10:08implemented row level security along the
10:11same lines we can also Implement column
10:13level security for example in any
10:16organization salary is confidential
10:17information and we don't want everybody
10:19to see the salaries of other people in
10:22that case you know we can actually
10:24create a view which hides salary column
10:27and then retrieves all the other columns
10:29let's quickly do that so obviously to do
10:32that we can create you know a view which
10:35returns non-confidential information so
10:38let's give it some meaningful name view
10:41non
10:44confidential
10:46data and we don't want this we Clause
10:48because we don't want to implement row
10:50level security we want to implement the
10:52column level security so from the select
10:54list I'm going to knock off the salary
10:57column and let's create this VI now so
11:00what's going to happen here we're only
11:03going to get
11:07the all the columns except the salary
11:10column so select star from that view
11:13look at that we get every column except
11:15the salary column since the employee
11:17does not have access to the underlying
11:19base tables there's no way he can get
11:21access to that salary column so views
11:24can be used as a mechanism to implement
11:26rule level and column level security and
11:29views can also be used to present only
11:31aggregated data for example let's say
11:33you want to prevent users the end users
11:35from looking at the detailed data you
11:37want to provide them only with aggregate
11:40data so how can we do that again using
11:44views okay so in your view different
11:46don't grant them access to the
11:48underlying base tables which will act I
11:50mean if you grant them access to the
11:52underlying base tables they can see
11:53everything so instead of that we can
11:55create a view which Aggregates data and
11:58then return them back
11:59and obviously to the to that do that
12:03create view let's call this
12:08view
12:12summarized
12:13data as what do we want we want let's
12:17say for example um if you look at this
12:19data I want the total number of
12:22employees by department for example in
12:24IT department how many employees are
12:26there in HR department how many
12:28employees are there there so we want
12:30total number of employees by Department
12:32the summarized data and obviously to do
12:35that we have to use Group by clause and
12:37we have seen how to use the group by
12:39clause in the previous uh sessions of
12:41this video
12:42series so we want the department name
12:45itself so
12:47select D
12:50name and then we want the count of
12:53employees so we want the count we use
12:55the count aggregate function so count of
12:58ID and let's say
13:02total
13:05employees from the tables which
13:11are TBL
13:16employee and we want to join that
13:19obviously with TBL
13:21department
13:24on TBL
13:26employee Dot
13:31Department ID is equal let's copy that
13:36TBL Department do Department ID and we
13:40want to use the group by Clause to group
13:43by Department
13:47name let's create this view F5 command
13:51completed successfully now let's select
13:53the data from this
13:55view so select star from
13:59view summarized
14:04data so now we get the aggregated data
14:08Department name and total number of
14:10employees so views can also be used to
14:13present aggregate data only and high
14:15detailed data and obviously to modify a
14:18view we can use alter view statement now
14:21to modify any object in SQL Server we
14:24use I mean for example alter table table
14:27name similarly alter procedure procedure
14:29your name along the same lines if you
14:30want to alter a view alter view view
14:33name and similarly if you want to drop a
14:35view you'll say drop View and view
14:39name on this slide you can find
14:41resources for asp.net uh cop and sqls
14:45server interview questions that's it for
14:47today thank you for listening have a
14:49great day

About this transcript

This page contains the full transcript of Views in sql server Part 39 by kudvenkat, generated from the public captions YouTube serves with the video. The transcript has 2,283 words across 331 segments, with the original timestamps preserved so you can click any line to jump to that moment in the embedded player.

What you can do with it

Use the transcript to take notes, quote the speaker, build a study guide, generate a summary with ChatGPT or Claude via the YouTube Summary tool, or export it as a timed subtitle file with YouTube to SRT. You can also re-open it in the transcriber to translate the transcript into 100+ languages.

Free YouTube transcript tool

YouTube2Text is a free YouTube transcript generator — no signup, no daily limit. Paste any YouTube link and get the full transcript instantly, with timestamps, click-to-jump, translation to 100+ languages, AI prompts for ChatGPT, Claude, and Gemini, and exports to TXT, SRT, VTT, or Markdown.