Multi statement table valued functions in sql server Part 32 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

hello welcome to plenum technologies I
am venket this is path 32 of sequel
server in this session we'll learn about
creating and calling multi statement
table valued functions and finally we
will also look at the differences
between inline and multi statement table
valued functions before continuing with
the session I strongly recommand to
watch parts 30 and 31 of this video
series multi statement table valued
functions are very much similar to
inline table valued functions with a
very few differences between them first
let's look at an example of creating an
inline and multi statement table valued
functions and then we will find the
differences between them I have this
employee table which has got ID name
date of birth gender and department ID
columns now what we want to do is we
want to create an inline and multi
statement table valued functions which
give us the same output that you can see
on the right hand side I just want the
ID name and date of birth okay so now to
do that we are creating an inline table
valued function here and we have spoken
about inline table where it functions
extensively in part 31 of this video
series ok so to create this function we
have fused create function and then
function name returns table okay because
we know that a function can take
parameters and it should return a value
okay so this function is not taking any
parameters and it's returning a table
and then we have this ask he word return
whatever is the Select statement
returning here the Select statement is
selecting ID name and date of birth type
casted as date from TBL employees table
so whatever this select statement is
retrieving from TBL employees table that
is being returned by this function okay
so this is an inline table valued
function and if you look at the inline
table valued function after returns you
have only the table keyword you are not
specifying the structure of the table
that gets returned from this inline
table valued function and another thing
to note here is that this function FN
ilt v underscore get employees this
function doesn't have a begin and then
clause okay now let's look at the multi
statement table value function okay so
whether
if it is in line table valued function
or scalar function or multi statement
table valued function you always use
create function statement to create a
function so create function and then the
name of the function here FN underscore
multi statement table valued function
underscore get employees M STV f stands
for multi statement table valued
function again the scope get employees
and then I am saying returns at table so
I am creating a table variable here okay
so at table is the name of the variable
and its data type is stable and
obviously if you are creating a table
variable a table variable has to have
you know the column names and the data
types so that's what we are creating
here in the bracket okay when we are
creating a table you will specify the
name of the column and its data type
similarly you know for a multi statement
table valued function you are saying
okay I am returning a table with this
structure which has got ID name and date
of birth okay so that's one of the
primary difference between in line table
valued function multi statement table
valued function an in line table valued
function you don't specify the structure
of the table but in multi statement
table valued function you do specify the
structure of the table okay and then as
and then you have this begin and end
block but if you look at the in line
table valued function you don't have
that block okay and within that begin
and end block you have the body of the
function okay so what are you doing here
here you are inserting data into this
table variable you're saying insert into
this table variable okay so what do you
want to insert into this table variable
you want to insert the ID name and date
of birth type casted as date from the
employees table so you are retrieving
all these records you know from
employees tables
you're basically retrieving ID name and
date of birth columns and then inserting
those rows into this at table variable
if you look at the at table variable it
has got ID name and date of birth
columns okay now if you look at date of
birth here in this table it's a day
den time column but here it's just a
date column so that's the reason why I
am typecasting this date of birth to be
of type and not only that if you look at
the output I just want the date I don't
want the time part of it so that's why
I'm just typecasting it to date type
which will truncate that time part and
just give me the date okay so you are
inserting that into this table and
finally you are saying return so when
when this function encounters this
return keyword what's going to happen
this function will return this table so
whatever you have inserted into this at
table variable gets returned to the
caller who ever calls your function he
will get the data that is present in
this table okay so if you look at the
differences you know the difference is
very simple to understand an inline
table valued function you don't specify
the structure of the table you just say
it returns a table and the structure of
the table that gets returned for an
inline table valued function is
determined by the Select statement that
you have there
okay so if you look at the multi
statement table valued function you do
define the structure for the table and
then in line and other differences in
line table valued function cannot have a
begin and end block whereas a multi
statement table valued function can have
a begin and end block okay now before we
continue to the you know added other
differences between these two functions
let's quickly create these functions and
then see how to call them so to create
an inline function look at this this
exactly what we have seen on the slide
create function function name returns
table as return whatever this query
returns look at this basically this
query selects this data ID name date of
birth okay so return this data from this
function that's what we are exactly
saying here so let's execute this
function and create this function so the
function got created so you go into that
database programmability folder
functions and since this is a table
valued in line table valued function you
look for that in the table valued
functions folder refresh that you should
see the function that we have just
created and all
obviously to call this function you will
say select star from that function name
so what's that function name FN
underscore IL TV and score get employee
so when I press f5 I get the result back
as expected and along the same lines if
I want to create a multi statement table
valued function you use the create
function create function function name
returns a table variable which has got
this structure and you are seeing this
variable is of type table and then as
begin and end blocks within that you are
saying okay into this table variable
insert ID name and date of birth from
TBL employees so this table has the rows
that are present in TBL employees but
only ID name and date of birth columns
and finally you are saying return which
will return this table variable back to
the caller so let's create this function
and obviously to select data from this
function you will say select star from
in line table valued functions and multi
statement table valued functions both of
them will be in this table valued
functions folder so this is our multi
statement table valued functions let's
drag and drop it and both I mean to call
an in line table valued function or a
multi statement table valued function
you use the same syntax select star from
the function name select star from the
function name so let's plus a5 and you
should see the output there okay
so we have seen how to create and call
an in line table valued function and a
multi statement table valued function
now let's find out the differences
between them okay we have already seen a
few of the differences in an in line
table valued function the returns class
cannot contain the structure of the
table the function is going to return
whereas with multi statement table
valued function we specify the structure
of the table that gets returned we have
seen that in line table valued function
cannot have begin and end blocked
whereas the multi statement table valued
function can have that in line table
valued functions this is import
and okay now if you look at what we have
done just now in the previous slide you
know this output that I want to achieve
I can achieve that both with an inline
table valued function as well as a multi
statement table valued function so when
you have two choices to do the same work
I would always prefer an inline table
valued function over multi statement
table valued function because of the
performance gain you know that I get
when I use inline table valued functions
so in line table valued functions are
better for performance than multi
statement table valued functions so if
the given task can be achieved using
both then always prefer in line table
valued functions and the reason for
improved performance and in line table
valued function is because internally
sequel a sequel server treats an inline
table valued function much like it
would've you okay
whereas a multi statement table valued
function you know is treated similar
like it would a stored procedure that's
why in line table valued functions are
better for performance reasons okay
another difference between an inline
table valued function and multi
statement table weld function is that
it's possible to underlying table using
an inline table value function but it's
not possible with multi statement table
value function let's understand what we
mean by this now if you look at this
inline table valued function this
function is actually getting its data
from the employees table so for this
function the underlying table is nothing
but the employees table now look at this
to retrieve the data from this function
I am calling it as if it is a table so I
am saying select start from this
function you know I'm we are almost
treating it like a table okay now if
when I call this function this function
actually execute this statement and
returns that data back to me and and
this statement is being executed on TBL
employees table so for this function
this employees table is the underlying
table now I am able to retrieve the data
from TBL employee's table through this
function now is it possible to add
and update the data through this
function absolutely you can do that
using an inline function let's do that
and see what happens so I'm saying
update this function okay update this
function said you know I want to set
Sam's name from Sam one to maybe Sam set
name is equal to Sam where Sam's ID is 1
so let's say ID is equal to 1 and see if
it updates that so when I execute this
let's see what happens now look at this
it says one row affected which means the
true got updated so now let's say select
star from the table and you see that Sam
has been updated to Sam one has been
updated to Sam as expected now let's try
to do the same thing if you look at this
you have this multi statement table
valued function now when I try to
retrieve data from that obviously I get
the output you know the same output but
can is you an update statement against
the State against this function and
update the underlying table it's not
possible ok but let's try to do that and
see what happens
ok instead of rewriting the query let's
copy this same query and change the name
of the function so instead of updating
the inline table valued function we want
to update the multi statement table
value function so let's do that and see
what's going to happen so I want to
update Sam to maybe Sam one I press F I
look at this object you know function
multi statement table valued function
get employees cannot be modified and it
makes sense why if you look at the
implementation of the multi statement
table valued function this function is
not directly getting its data from TBL
employees I mean it's getting from TBL
employees but then it it is being
inserted into this table variable and
that table variable is getting you know
returned back to the caller ok
so for that reason you know in a multi
statement table valued funk
as you can have many intermediate
processing steps there you don't really
know which column is coming from which
table and and from which fine
you know variable we are finally
returning the data back so there will be
many intermediate processing steps there
and it doesn't really make sense in
allowing to update the underlying tables
using the multi statement table valued
functions and that's the reason why
sequel server doesn't allow us to do
that okay so that's another difference
between multi statement table valued
function and inline table valued
function on this slide you can find
resources for asp.net and C sharp
interview questions that's it for today
thank you for listening