Derived tables and common table expressions in sql server Part 48 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

hello welcome to premium technologies I
am venket this is part 48 of sequel
server in this session we'll learn about
derived tables in common table
expressions we'll compare these with
other constructs that are available in
sequel server like views table variables
local and global temporary tables let's
start with an example I have TBL
department table here which is called
department ID and Department name
columns TBL employee table here which
has got ID name gender and department ID
columns now let's say I want you to join
these two tables and produce the output
that you can see on the right-hand side
here I want the department name and the
total number of employees within that
department but then your list should
only include those departments which has
got two or more employees and obviously
to achieve this output we have several
ways but then we will see how to do that
using views okay so we are creating a
view here create view view name as we
are selecting the department name
department ID and count of star which
would count the number of employees
within that department and we are giving
it an aleeah's called total employees
from TBL employee join with TBL
department and obviously between these
two tables department ID is the column
that's common so we are joining those
two tables on that column and you will
have to group by department name
department ID because we want the count
and we have spoken about joins and grew
by in a very great detail in the
previous sessions of this video series
so please check that part and we have
spoken about views as well ok so we are
creating this view so once we have this
view it's very easy to compute this
result that you want so the columns that
you want or the department name and
total employees so obviously we have
those columns in the view department
name and total employees all you have to
do is select those columns from that
view and then select only those
departments which has got total
employees count greater than or equal to
2 which will give us this output ok but
then let's say if we are using this view
only within this query then it doesn't
really make sense to create a view for
that purpose that's why we have other
constructs in sequel server which we'll
be exploring next so views get saved in
the database and can be available to
other queries and stored procedures as
well so if you are you know using this
view in other stored procedures and and
you know in other queries then that's
fine but then if you are using this as a
one-time you know just this one time in
this query then then it doesn't really
make sense to have this view created we
can make use of other constructs that
are available in sequel server like CDE
derived tables temp tables table
variables etc which we'll be talking now
all right let's look at this in action
so we have these two tables TBL employee
TBL department you know the data
okay so let's create this view execute
command completed successfully so when
we refresh this folder we should see the
view there so views get physically
stored in the database now if you want
the columns from that view look at this
we have all the columns from the view
select star from if you say view it
would return all the columns within the
view we have got three columns
Department name Department ID and total
employees but then in our output we are
only expecting department name and total
employees so we are only selecting the
required columns department name total
employees from that view where total
employees is greater than or equal to
two which would return IT and HR
departments so let's execute that query
and we only get that all right but then
if you are using this view just in this
query then it doesn't really make sense
to create a view all right let's see how
to achieve the same thing using
temporary tables so obviously the logic
to calculate the department name and
total employees it's exactly the same
you know we have this query this query
will not change it's just that we are
going to use that with other with
the constructs that are available in
sequel server so here we are using
temporary tables okay so the query is
the same select department name
department ID count of star as total
employees we are giving it an aleeah's
and we are selecting these columns into
hash temp employee count this is the
temporary table so you might be
wondering where did you define the
columns that this temporary table has
you know when you use select into syntax
what's going to happen behind the scenes
sequel server will create those columns
for us automatically you don't have to
define the structure of the temporary
table so we are selecting these columns
into this temporary table you know the
rest of the logic is the same we are
joining TBL employee with TBL department
on department ID column and grouping
them by department name and department
ID columns okay so this logic remains
them it's just that you are selecting
that data into this temporary table and
then what you are doing it's pretty much
same after that you want the department
name TBL total employees instead of the
view you are saying from the temporary
table where total employees count is
greater than or equal to two okay let's
look at the set in action and remember
since this is a temporary table it's a
good practice to actually drop the table
after you have finished using it and
remember there are two types of
temporary tables local temporary tables
and global temporary tables depending on
the type of temporary table you are
creating the scope differs okay and
temporary tables are stored in temp DB
local temporary tables are visible only
in the current session and can be shared
between nested stored procedures meaning
if you have created a stored procedure I
mean a temporary table in stored
procedure a and if stored procedure a
calls stored procedure B then the
temporary table that you have created in
stored procedure a will be available in
stored procedure B so local temporary
tables are visible in the current
session only in the transition and can
be shared between nested stored
procedure calls
whereas global temporary tables are
visible to all the sessions and a
destroyed
the last connection referencing the
table is closed that global temporary
table is basically closed let's look at
this in action so we are selecting these
columns Department name Department ID
count of star into this temporary table
so let's execute that so we should have
the temporary table created now so
obviously when we say select star from
the temporary table you should see all
the rows and columns so select star from
the temporary table which here is temp
employee count since we have a single
pound sign it's a local temporary tables
we have spoken about temporary tables in
a great detail in the previous sessions
of this video series so if you're new
check those paths first so we have these
columns so obviously if you want only
those you know Department name and total
employees columns you select those two
columns from that temporary table where
total employees is greater than or equal
to 2 so when we execute this query we
get the expected output so the output is
exactly similar except that here we are
making use of temporary tables instead
of a view all right
now let's look at how to do exactly the
same thing using a table variable now
when we created the temporary table we
didn't define the structure the
structure is automatically inferred
based on the Select statement that you
are using here because you're using
select into but then when you are using
table variable here we are defining the
structure explicitly so declare a table
variable I mean a variable and this
variable is of type table obviously if
it's a table a table will have columns
and data types so you'll have to define
them as well so this table is going to
have department name department ID and
total employees just like you know how
we have this view department name
department ID and total employees
columns ok so next what we are doing we
are inserting into that
table variable department name depart
this logic remains the same the query is
exactly the same we are selecting
department name department ID count of
star from TBL employee joining that with
TBL department on department ID columns
and then finally grouping it by
department name and department ID so now
we have this table variable from the
table variable you can select the
required columns Department name and
total employees where total employees is
greater than or equal to 2 so it's
exactly similar in the previous example
we were using temporary table in this
example we are using a table variable ok
just like temporary tables a table
variable is also created in the temp dB
you know most of the people think table
variable is actually created in memory
which is not true both temporary tables
and table variables are created in the
temp TB the scope of a table variable is
the batch or the stored procedure or a
statement block in which it is declared
but the advantage of using table
variables is that you can pass them as
parameters between procedure calls
between one procedure to another
procedure ok so let's look at this in
action obviously it's a very good
practice to drop temporary table so
let's drop that table I mean even if we
don't drop a local temporary table it
gets automatically dropped in the
session that has created it is closed so
using table variable we are creating the
table structure here declare the table
variable specify the column names and
the datatypes and then insert into that
table variable and then select whatever
columns you want from that table
variable so let's execute this we should
see the same output except that we are
using the table variable and the
advantage of using table variable is
that you don't have to drop at like
temporary tables but there are several
other differences as well between table
variables and temporary tables
all right so we can also achieve the
same thing using derived tables now look
at this when we created a table variable
we will have to define the columns and
the data types for a table variable but
whereas if you are using a derived table
this table is derived so you don't
really define a table as such you just
give it an Aaliyah's so if you look at
this this is the query that we have been
looking at all this while select
department name Department ID count of
star s total employees from TBL employee
joining that with TBL department the
same query here now if you look at this
this query is wrapped between these
brackets and then you're seeing as
employee count so this entire thing from
this bracket to this bracket you know is
considered as a table okay it derived
table and you are giving that table a
named employee count and then if you
look at this the columns that are
present in this table are nothing but
the columns in the Select list here so
you have department name department ID
and total employees columns okay so now
you treat this from here till here as a
table so you treat this as a table
employee count so select department name
total employees from this employee count
where total employees is greater than or
equal to 2 so here you know this is a
derived table this employee count is a
derived table and derived tables are
available only in the context of the
current query here the Select statement
if you have another select statement you
know beneath that this derived table is
not available for that select statement
it's only available here within this
context so let's look at this in action
so if you look at this one if I execute
this it gives us those columns
Department name Department ID and total
employees all those columns but then
what we are essentially doing here is
wrapping that inside this bracket and
then giving it in name and this name can
be treated as a table by itself which
has got these three columns and what you
are doing
from these three columns you're
selecting just Department name and total
employees columns and then you're
filtering on that column so where total
employees is greater than or equal to
two so obviously we only get those two
rows where the employees count is
greater than or equal to two which in
this case is ID and H are just like
other examples that we have seen and
finally we will see how to achieve the
same thing using something called CDE
common table expression we will be
talking about si te s in a great detail
in the next session this is just an
introduction to CDE so achieving the
same output with using a CDE
common table expression and CTS are
introduced in sequel server 2005 so if
you look at this one this is again very
simple okay so the exact same query that
we have been working with this query
that you can see here now what we have
done is we use this keyword vert and
then you have given a name this is
nothing but the common table expression
name CTE name so with employee count is
the name of your CTE and then you can
specify the columns that your CTE
returns okay this part is optional if
this query is returning a unique column
names then you don't have to define this
column list okay but I have defined it
here just to be clear so we this common
table expression employee count is
having three columns department name
department ID total employees okay where
are these columns coming from these
columns are coming from the Select query
so this select query look at this we
have the brackets here so this is like a
table what is the name of this table
employee count
and this is a CTE so we use the wit
keyword so usually the syntax for CTE is
in such a way that you use the width
keyword with CTE name the columns that
your CTE returns as and your select
query your select query actually
determines what columns are being
returned by the CDE alright so we are
done this is pretty much similar to
derived table now if you look at this
derived table this query
you know returns I mean it's like a
table you're just naming it as employee
count along the same lines for a CTE
this query and you're saying with CTE
name and the columns as and then your
query obviously then what you can do is
since this is treated as a table now a
common table expression and this common
table expression has got three columns
just like derive table and out of those
three columns we just want the
department name and total employees
columns from that CD which is employee
count here where total employee is
greater than or equal to two so we get
the same output let's look that in
action so that's the CTE so let's
execute this and we should get the same
output all right a CTE can be thought of
as a temporary result set it's a
temporary table or you can also think of
as I derived a table that is defined
within the execution scope of a single
select insert update delete or create
view statement you can use this city
even within a view okay but this is
defined only within the execution scope
of a single select insert update delete
or create view statement of this is a
little confusing what we mean by this we
are going to talk about common table
expressions in a great detail in the
next session I will explain this
definition clearly you know in the next
video session and a CTE is a similar is
similar to a derived table in that it is
not stored as an object and lost only
for the duration of the query so this is
not being stored as a temporary table or
a view okay it only lasts for the
duration of this query outside the
context of this query the city it
doesn't really have any meaning we'll
talk about CD is in a great detail in
the next session of this video series 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