Hello welcome to doubt sessions
of questpond so we have
in this session you know will clarify the doubt
about CTE or
common table expression so here’s a email we have got
from one of our users I want to
clarify common table expression in SQL I have
ask many times but did not respond to me
please clarify okay
so what I will do is let me
just quickly go to first to MSDN
and let’s just talk about definition and then
with then will do some demonstration
okay you can see that a common
table expression nothing but it’s a
temporary result set it’s a temporary
table okay and
you know on this temporary table right you know we can
go and do insert update and delete but
within that scope so in other words
I cannot use this common table expressions
if let say I have create a common table expression
in stored procedure right in one stored procedure
I cannot use in other stored
procedure so in short
you know what we can says that you know common table expression
is like derive table it’s temporary result set
okay so
let me just show you demo here so you can see that I have a
very simple table here called as TBL
customer and it has like you know some
three or four five fills here okay
now let say that we don’t want to
see all of these fills you know we just want must
be just the customer code and customer name
that’s it so we want to create
very simple temporary result set
you know which will have customer code and customer name
so let me just go back and flip to
MSDN here so
if you see the syntax of the common table
expression the way they write it says
that with the common
table expression name and then what
fills you want okay
so let me just go and create
a common table expression here
so in other words you know what we like to do
here is
you know this five fill table what we
have we like to create a very simple
temporary table which has only two fills that is
customer code and customer name and we will
like to use that temporary table you know for a short
duration while the select queries getting executed
and then we don’t want it so the way
to write a CTE you can see the
syntax here with CTE name
and how many fills you want so
currently let say that we just want customer code and customer
name so the way will write the CTE is
the
syntax is with so
CTE name I will say name as customer
light and what do
I want I want
the customer code
and
customer name
right and you can see that it's
followed you know web with a
round bracket so as
when we have round bracket
first round bracket and second round bracket
and will say that
select only
customer code
, customer name
you know from
tbl _customer
okay right
now you can see that we have created a
CTE with the name called as customer
light okay so in other words now if I go
and select customer light
here so now if you do
select here if I say select
star from
customer light you can see
their customer light has now popped up as common
table expression CTE here
and if execute this i can only see now
two columns as compare five columns
in the previous table so as said in
MSDN CTE is nothing but it can be thought as
temporary result set and
you know on this temporary result set always go and do
selects you can do insert updates and
deletes okay and
if you see further right what is use of CTE
because many people confuse CTE with
temp tables right so
I will not discuss you know those things at this
moment in this doubt solving session here must be I can
create a separate video you know what’s difference
between CTE and temp tables and views
okay but you know very
shortly if you see what is MSDN says
is that a CTE can be use to create
recursive query in other words I can create CTE
and call a recursively
second you know it’s a substitution also for the views
if you see views are nothing but it’s
it’s again like temporary
table right but you know when
sometimes you don’t want store that meta data like in
view we actually create view right and then view actually
get stored in to SQL server so some time you
don’t want to create meta data you want to just create
something on run time like this temporary table
use it and then finish it of so
at that time agin you know CTEs you know very useful
and you know finally
if you see right CTE also offers advantages
improving your SQL
readability right
so I hope that you know this
doubt solving session has clarified what is a
use of CTE and
you know what exactly it us
Thank You thank you Very Much.....