Is Table-Valued UDF is Performance Killer

One my previous article "Can view takes input parameters" I got some response from various sources like "Linked In" etc.

http://sqlknowledgebank.blogspot.in/2012/05/can-views-take-input-parameters.html

Here some of my readers give me some comments like

"A view might be more efficient than a table valued functions"

I searched different blogs and article related to it and find that there are a common misconnect is the Table value function is a poor performer.

So I decide to wire this article related to the performance factor of Table Valued function by collecting some notes from different sources.

How many type of Table valued function does MS SQL Server supports

There are 2 types of table valued function

1.    Single-statement table valued user defined functions, also called in-line

2.     Multi-statement table valued user defined functions

The simple example of both is mentioned bellow

Single Statement Table Valued function

CREATE FUNCTION dbo.fn_TableVal

                (@p_sal DECIMAL(20,2))

RETURNS TABLE

AS RETURN

(

      SELECT *

      FROM   emp_matsre

      WHERE  empSal>@p_sal

)

GO

Multi-statement table valued Function

CREATE FUNCTION dbo.fn_TableVal

                (@p_sal DECIMAL(20,2))

RETURNS @Ret_Table TABLE

        (idno    INT,

         empName VARCHAR(50),

         empSal  DECIMAL(20,2))

AS

BEGIN

    INSERT INTO @Ret_Table

      SELECT *

      FROM   emp_matsre

      WHERE  empSal>@p_sal

     

      RETURN

END

GO

The single-statement table valued function sometimes called the views with parameters.

Execution of these UDF's will be incorporated directly into the execution plan with the rest of the associated query. Indexes may be used if the query within the UDF is well constructed. It is generally safe constructs to use within your queries.

Multi-statement UDF's are built upon the structure of table variables.

These are not incorporated within the rest of the execution plan and usually show up as a table scan against a table with one row. The problem is your query may have more than one row. When the multi-statement UDF's are used on large sets of data they cause very serious performance problems. They won't scale well, and the performance issues are masked by the apparent low cost of the operations involved. We need to be extremely careful when using the multi-statement table valued UDF's. The problem is that the system sees the BEGIN/END and runs the PROCEDURE in a different context. Without it, it takes the subquery in the return() call and can simplify it out.

Non-inline functions do not contribute towards the cost reported by an execution plan. You need to use Profiler to see their effect.

Hope you like it.

Posted by: MR. JOYDEEP DAS