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
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 ua-cam.com/users/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. ua-cam.com/video/y780MwhY70s/v-deo.html If you want to receive email alerts, when new videos are uploaded, please subscribe to my youtube channel. ua-cam.com/users/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
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?
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 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?
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 ??
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
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 )
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!
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
Excellent . Kudvenkat is a really nice human , He is my best technical teacher ever . Slam to you man :)
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.
Each video about SQL is very clear and so informative. I felt very useful of this. Thank you.🤩🤩
Great compilation. There is in-depth explanation of the Concepts.
Thanks a ton Venkat!
What can I see? Perfect as always! I hope to end this whole SQL course !
2018 and still , your videos are amazing !!
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
Clear and Very helpful 👍
In 2023 your videos are most valuable videos
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
ua-cam.com/users/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.
ua-cam.com/video/y780MwhY70s/v-deo.html
If you want to receive email alerts, when new videos are uploaded, please subscribe to my youtube channel.
ua-cam.com/users/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
Thank you for the session
Good video about in line and multi value function.
extremely very very good
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?
Kudos!
aha TNX A LOT BRO .. FOR YOUR TIME
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?
Is this the only video in which you haven't said "have a good day" at the end of your video?? :P
Great
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, you have not uploaded videos on nested or inner/outer query on sql server.
which is more useful mstv function or itvf function
sir...can you explain, in which situation we use the MSTV function...?
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?
I understand that in MSTVF doesn't use update statement but retieve same tabel
Can we use joins in ILTVF and MLTVF.
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?
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!!
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
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 )
What are the advantages using Inline Table Valued Functions over Views?
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!
and also, does not completed remaining indexes. please cover that much also...
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
Why would amybody use multiline if it's slower and rigid? Security maybe?
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
anyone in 2025?