YT YouTube2Text

YouTube2Text → Transcripts → Multi statement table valued functions in sql server Part 32

Multi statement table valued functions in sql server Part 32 — Transcript

by kudvenkat · 2,371 words · 345 segments · language en · Watch on YouTube

Open in transcriber Summarize with AI Download SRT/VTT

Full transcript

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

About this transcript

This page contains the full transcript of Multi statement table valued functions in sql server Part 32 by kudvenkat, generated from the public captions YouTube serves with the video. The transcript has 2,371 words across 345 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.