In one of my recent articles, I mentioned the use of Table Valued Function (TVF) instead of Stored Procedure (SP). I received a follow up email asking what type of SP can be converted into a TVF. This is indeed a very interesting question! In fact, not all the SPs qualify to be converted to a TVF.
Please note that I am not encouraging to convert all the SPs to TVFs. Each SPs have their own usage and need. Here, I shall discuss about the type of SP that can be converted to a TVF.
First of all, you need to convert the SP that you are using to retrieve data and to insert it into any other table. Please read my original post wherein I have explained the scenario for the same. If you do not have to insert the data from the SP into another table, then there is no need to convert the SP to a TVF. This conversion will not necessarily enhance the performance.
In general, SP that returns only one resultset qualifies for conversion to TVF. If there are more than one resultset, then the SP cannot be converted to TVF as the TVF always returns one resultset. Moreover, if the SP has DML statements such as DELETE, UPDATE, INSERT or MERGE, it cannot be converted to a TVF. The usage of TVF is limited to the SELECT statement only. If the SP must be executed by using EXEC, it cannot be converted to TVF.
Do you know any other reason why a SP needs to be converted to a TVF?
Reference: Pinal Dave (http://blog.sqlauthority.com)
Well, this is indeed a good discussion. according to me, if we are having just one SELECT statement which is returning one result sets than it would be good to convert it into TVF because we can join that result set to other SELECT statement.
while using SP, you can’t join it with anything else directly.
Hi Pinal,
Can you provide a link to the original article about converting stored procedures to TMVs?
Sure,
I am reposting my original article in a day or so, as I found something new in regards to that article.
Kind Regards,
Pinal
how to insert data in two tables to use one Store Procedure
You said “Moreover, if the SP has DML statements such as DELETE, UPDATE, INSERT or MERGE, it cannot be converted to a TVF. The usage of TVF is limited to the SELECT statement only.”
That is true of an in-line TVF, but not true of a multi-statement TVF. If you could not INSERT into the table defined in the return of a multi-statement TVF there would not be anything to RETURN.
It would be correct if you added the exception stated in Books Online, “except to table variables”.
Thanx :)
Hello! Could you give me a link to the original article about converting a stored procedure into a table valued function? I can’t find any obvious links to it from this page…
Thank you!
I have a question about the TVF. When performing a SELECT on the TVF, the result was return in less than a second. But when the TVF is used in a join statement, it took about 4 minutes to run! I was wondering why it’s so slow when used in the JOIN?
On the other hand, if the TVF is loaded first into a temp table, then use this temp table in the JOIN, the result was under 15 seconds.
Hello Ting,
I think when used in JOIN, the TVF executed multiple times (depends on the number of records in other joined tables)while in SELECT statement, it is executed once.
So using the temporary table is better way to process its result.
Regards,
Pinal Dave
Hi Pinal Dave,
I have an issue with a Table valued fuction in SQL Server 2005.
In this function contain 4 CTEs and return around 35000 rows.
Now the issue is, sometimes it wont produce any result and got hang, but most of the times it will return the result within 2-5 seconds. I droped and recreated the funcion When I got the issue and that time it was working fine. Now daily one or two times this is happening.
I have checked the memory, TempDB, server space and all are fine.
Can you please suggest a solution for this issue.
Thanks and Regards
Thomas