SQL SERVER – Converting Stored Procedure into Table Valued Function

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)

About these ads

10 thoughts on “SQL SERVER – Converting Stored Procedure into Table Valued Function

  1. 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.

  2. 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”.

  3. 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!

  4. 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.

  5. 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

  6. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s