In general from a performance perspective always, prefer Inline Table Valued function(ITVF) over Multi-Statement table valued function(MSTVF), as the ITVF will perform better than the MSTVF. This is because, SQL Server treats ILTVF much like a view, hence it has the capability of using the table statistics, where as, this is not the case with MSTVF.
Here in 2022. Very good few videos on UDFs. Been studying for a job interview and walked away feeling like I have a pretty clear understanding of this SQL. Thank you for your work and making an advanced topic understandable to many.
An Inline Table Valued function(ITVF) body contains just a single TSQL statement, where as Multi-Statement table valued function (MSTVF) can contain multiple TSQL statements. So, if the table that is being returned by the function, requires several intermediate processing steps and if cannot be achieved with ITVF, then we prefer MSTVF.
This is exactly what I am looking for because in the video all the differences mentioned are actually the advantages of ITVF. So I believe someone must have got the same question in the mind and chances are you have given the answer already. So I looked through the posted comments and have found it here. Thanks again.
Hi Suman, Returns @Table table (Id int, Name nvarchar(20), DOB Date) The first RETURNS statement, just specifies that, the function is returning a table with the specified structure The RETURN statement in the body of the function, signalls that, all the processing is done, and the Table specified in in first RETURNS statement can now be returned to the caller of the function
Hi. I just ran a Store Proc and View created on the same table. However, for me the performance of the Store Procedure was slightly better than that of the View. Now how would you explain that?
hi venkat, can u please tell me why are you using two return statements in multi statement table valued functions, the return statement returns the @table but which return statement does that
Wonderful video. Thanks a lot for such in depth explanation. Can you please tell me if underlying tables are updated, will that modify the inline table valued function immediately or we have to do any additional steps so that function comes in sync with the underlying table?
Thank you very much for taking time to give feedback. This means a lot. I am very glad you found the videos useful. I have organised all the Dot Net & SQL Server videos in to playlists, which could be useful to you ruclips.net/user/kudvenkatplaylists?view=1&sort=dd If you need DVDs or to download all the videos for offline viewing please visit www.pragimtech.com/kudvenkat_dvd.aspx Slides and Text Version of the videos can be found on my blog csharp-video-tutorials.blogspot.com Tips to effectively use my youtube channel. ruclips.net/video/y780MwhY70s/видео.html If you want to receive email alerts, when new videos are uploaded, please subscribe to my youtube channel. ruclips.net/user/kudvenkat If you like these videos, please click on the THUMBS UP button below the video. May I ask you for a favor. I want these tutorials to be helpful for as many people as possible. Please share the link with your friends and family who you think would also benefit from them. Good Luck Venkat
Sir one thing I can't understand that if we get all the benefits from Inline table valued function then why should we use Multi statement table valued function..just for the security reason?
Thanks a lot for your nice video. How can you selectively call data from the MSTV function you created? (I mean, you can call data as 'Select *from [MSTVF_Name]()'. But, I want to call lists based on input parameters, let say only Gender = Female and Department = HR?
Hi. Thank you for these videos... But I didn't understand this video I didn't know that if Inline Table Valued function(ITVF) and Multi-Statement table valued function(MSTVF) retrieve same table then why do we always use ITVF?
What would be the difference between a multi-statement table valued function and a view with schemabinding? They look the same to me! (except that I wouldn't be able to parameterize )
I noticed that when we update the inline table value function, the original table also gets updated. Is there anyway to update the inline table but not the original table
getting error as invalid column name while updating table using inline function.Same is getting updated using normal query.. What can be the real cause
Can we insert two tables at a time I mean Insert into @table Select I'd,name,dob from tblEmployee select deptid, deptname from Department By passing additional required parameters
Thanks for the video, I would like to request if you can demonstrate a scenario where Inline function cannot be used and only multi value should be used OR vice versa. Thanks in advance!
Ok, everything was going smoothly until I saw Functions performing DML Operations (insert,update,delete). We all know the basic difference between stored procedure and functions is Stored Procedures can perform DML operations while functions can not. How come inline functions are able to perform insert, update etc ??
![[4K] Watch SpaceX Catch A Starship Rocket From Space!!! #IFT5](http://i.ytimg.com/vi/pIKI7y3DTXk/mqdefault.jpg)
In general from a performance perspective always, prefer Inline Table Valued function(ITVF) over Multi-Statement table valued function(MSTVF), as the ITVF will perform better than the MSTVF. This is because, SQL Server treats ILTVF much like a view, hence it has the capability of using the table statistics, where as, this is not the case with MSTVF.
Here in 2022. Very good few videos on UDFs. Been studying for a job interview and walked away feeling like I have a pretty clear understanding of this SQL. Thank you for your work and making an advanced topic understandable to many.
Excellent . Kudvenkat is a really nice human , He is my best technical teacher ever . Slam to you man :)
An Inline Table Valued function(ITVF) body contains just a single TSQL statement, where as Multi-Statement table valued function (MSTVF) can contain multiple TSQL statements. So, if the table that is being returned by the function, requires several intermediate processing steps and if cannot be achieved with ITVF, then we prefer MSTVF.
Each video about SQL is very clear and so informative. I felt very useful of this. Thank you.🤩🤩
This is exactly what I am looking for because in the video all the differences mentioned are actually the advantages of ITVF. So I believe someone must have got the same question in the mind and chances are you have given the answer already. So I looked through the posted comments and have found it here. Thanks again.
What can I see? Perfect as always! I hope to end this whole SQL course !
2018 and still , your videos are amazing !!
Great compilation. There is in-depth explanation of the Concepts.
Thanks a ton Venkat!
Clear and Very helpful 👍
Thank you for the session
In 2023 your videos are most valuable videos
Hi Suman,
Returns @Table table (Id int, Name nvarchar(20), DOB Date)
The first RETURNS statement, just specifies that, the function is returning a table with the specified structure
The RETURN statement in the body of the function, signalls that, all the processing is done, and the Table specified in in first RETURNS statement can now be returned to the caller of the function
Hi. I just ran a Store Proc and View created on the same table. However, for me the performance of the Store Procedure was slightly better than that of the View. Now how would you explain that?
hi venkat, can u please tell me why are you using two return statements in multi statement table valued functions, the return statement returns the @table but which return statement does that
Wonderful video. Thanks a lot for such in depth explanation. Can you please tell me if underlying tables are updated, will that modify the inline table valued function immediately or we have to do any additional steps so that function comes in sync with the underlying table?
this man is god!
Thank you very much for taking time to give feedback. This means a lot. I am very glad you found the videos useful.
I have organised all the Dot Net & SQL Server videos in to playlists, which could be useful to you
ruclips.net/user/kudvenkatplaylists?view=1&sort=dd
If you need DVDs or to download all the videos for offline viewing please visit
www.pragimtech.com/kudvenkat_dvd.aspx
Slides and Text Version of the videos can be found on my blog
csharp-video-tutorials.blogspot.com
Tips to effectively use my youtube channel.
ruclips.net/video/y780MwhY70s/видео.html
If you want to receive email alerts, when new videos are uploaded, please subscribe to my youtube channel.
ruclips.net/user/kudvenkat
If you like these videos, please click on the THUMBS UP button below the video.
May I ask you for a favor. I want these tutorials to be helpful for as many people as possible. Please share the link with your friends and family who you think would also benefit from them.
Good Luck
Venkat
Good video about in line and multi value function.
aha TNX A LOT BRO .. FOR YOUR TIME
Kudos!
Sir one thing I can't understand that if we get all the benefits from Inline table valued function then why should we use Multi statement table valued function..just for the security reason?
Thanks a lot for your nice video. How can you selectively call data from the MSTV function you created? (I mean, you can call data as 'Select *from [MSTVF_Name]()'. But, I want to call lists based on input parameters, let say only Gender = Female and Department = HR?
which is more useful mstv function or itvf function
extremely very very good
sir, you have not uploaded videos on nested or inner/outer query on sql server.
Is this the only video in which you haven't said "have a good day" at the end of your video?? :P
Hi. Thank you for these videos... But I didn't understand this video I didn't know that if Inline Table Valued function(ITVF) and Multi-Statement table valued function(MSTVF) retrieve same table then why do we always use ITVF?
sir...can you explain, in which situation we use the MSTV function...?
Can we use joins in ILTVF and MLTVF.
What would be the difference between a multi-statement table valued function and a view with schemabinding? They look the same to me! (except that I wouldn't be able to parameterize )
Great
I noticed that when we update the inline table value function, the original table also gets updated. Is there anyway to update the inline table but not the original table
I understand that in MSTVF doesn't use update statement but retieve same tabel
getting error as invalid column name while updating table using inline function.Same is getting updated using normal query.. What can be the real cause
Can we insert two tables at a time
I mean
Insert into @table
Select I'd,name,dob from tblEmployee
select deptid, deptname from Department
By passing additional required parameters
Thanks for the video, I would like to request if you can demonstrate a scenario where Inline function cannot be used and only multi value should be used OR vice versa.
Thanks in advance!
Ok, everything was going smoothly until I saw Functions performing DML Operations (insert,update,delete). We all know the basic difference between stored procedure and functions is Stored Procedures can perform DML operations while functions can not. How come inline functions are able to perform insert, update etc ??
Was Thinking the same!!
Why would amybody use multiline if it's slower and rigid? Security maybe?
and also, does not completed remaining indexes. please cover that much also...
What are the advantages using Inline Table Valued Functions over Views?