Multi-Statement Table Valued Function VS Inline Table Valued Function

Multi-statement Table Valued Function vs Inline Table Valued Function

In researching Matt's comment, I have revised my original statement. He is correct, there will be a difference in performance between an inline table valued function (ITVF) and a multi-statement table valued function (MSTVF) even if they both simply execute a SELECT statement. SQL Server will treat an ITVF somewhat like a VIEW in that it will calculate an execution plan using the latest statistics on the tables in question. A MSTVF is equivalent to stuffing the entire contents of your SELECT statement into a table variable and then joining to that. Thus, the compiler cannot use any table statistics on the tables in the MSTVF. So, all things being equal, (which they rarely are), the ITVF will perform better than the MSTVF. In my tests, the performance difference in completion time was negligible however from a statistics standpoint, it was noticeable.

In your case, the two functions are not functionally equivalent. The MSTV function does an extra query each time it is called and, most importantly, filters on the customer id. In a large query, the optimizer would not be able to take advantage of other types of joins as it would need to call the function for each customerId passed. However, if you re-wrote your MSTV function like so:

CREATE FUNCTION MyNS.GetLastShipped()
RETURNS @CustomerOrder TABLE
    (
    SaleOrderID    INT         NOT NULL,
    CustomerID      INT         NOT NULL,
    OrderDate       DATETIME    NOT NULL,
    OrderQty        INT         NOT NULL
    )
AS
BEGIN
    INSERT @CustomerOrder
    SELECT a.SalesOrderID, a.CustomerID, a.OrderDate, b.OrderQty
    FROM Sales.SalesOrderHeader a 
        INNER JOIN Sales.SalesOrderHeader b
            ON a.SalesOrderID = b.SalesOrderID
        INNER JOIN Production.Product c 
            ON b.ProductID = c.ProductID
    WHERE a.OrderDate = (
                        Select Max(SH1.OrderDate)
                        FROM Sales.SalesOrderHeader As SH1
                        WHERE SH1.CustomerID = A.CustomerId
                        )
    RETURN
END
GO

In a query, the optimizer would be able to call that function once and build a better execution plan but it still would not be better than an equivalent, non-parameterized ITVS or a VIEW.

ITVFs should be preferred over a MSTVFs when feasible because the datatypes, nullability and collation from the columns in the table whereas you declare those properties in a multi-statement table valued function and, importantly, you will get better execution plans from the ITVF. In my experience, I have not found many circumstances where an ITVF was a better option than a VIEW but mileage may vary.

Thanks to Matt.

Addition

Since I saw this come up recently, here is an excellent analysis done by Wayne Sheffield comparing the performance difference between Inline Table Valued functions and Multi-Statement functions.

His original blog post.

Copy on SQL Server Central

When would you use a table-valued function?

Table-valued functions are "just" parameterized views. This makes them extremely powerful for encapsulating logic that would otherwise be hidden behind an opaque stored procedure. Here's an example:

Inline Table-valued Function:

create function dbo.GetClients (
    @clientName nvarchar(max) = null
)
returns table
return (
    select *
    from dbo.Clients as a
    where ((a.ClientName = @clientName) or a.ClientName is null)
);

Stored Procedure:

create procedure dbo.usp_GetClients (
    @clientName nvarchar(max) = null
)
as
begin;
    select *
    from dbo.Clients as a
    where ((a.ClientName = @clientName) or a.ClientName is null)
end;

Unlike the stored procedure call, a table-valued function allows me to compose the logic from dbo.GetClients with other objects:

select *
from dbo.GetClients(N'ACME') as a
join ... as b
    on a.ClientId = b.ClientId

In such situations I cannot imagine using a stored procedure because of how restrictive it is when compared to the table-valued function. I would be forced to marshal the data around myself using a temp table, table variable, or application layer in order to combine results from multiple objects.

Inline table-valued functions are especially awesome because of the "inline" bit which is probably best explained here. This allows the optimizer to treat such functions no differently than the objects they encapsulate, resulting in near optimal performance plans (assuming that your indexes and statistics are ideal).

Performance using Inline table valued function to encapsulate SQL code

Can inline table valued functions (ITVF) be used to encapsulate and reuse code?

Yes. And they are superior in this to multi-statement TVFs because with multi-statement TVFs the encapsulation prevents the query optimizer from pushing predicates into the TVF logic, and prevents it from accurately estimating the number of rows returned.

Or will this result in performance issues?

Short answer, not typically.

Longer answer:

There are 4 ways to encapsulate and reuse query logic (whole queries, not just scalar expressions).

1. Views
2. Inline Table Valued Functions
3. Multi-Statement Table Valued Functions
4. Temporary Tables
5. Table Variables

Views and Inline TVFs don't inherently degrade performance, but they add to the complexity of query optimization.

Where the optimizer fails to consistently find low-cost plans you may need to intervene. A common way you can do that is by forcing spooling (ie materializing) of intermediate results, for instance replacing an Inline TVF with a multi-statement TVF, or by spooling results to a temp table ahead-of-time.

Spooling reduces the complexity of the encapsulating query at the cost of possible optimization of the encapsulated query when run in the context of the larger query.

When spooling results, Temp Tables are typically the best, as SQL Server they can have indexes and statistics that enable SQL Server to accurately assess the cost of the plans that will consume the intermediate results.

Table-valued Functions - Incorrect syntax near the keyword 'Begin'

You should declare your table name like below

RETURNS @Result

Then you need to insert into @Result table instead.

Your entire code should be like this

CREATE FUNCTION [BCF].[fn_GetSpecialLastCurrencyRateByDate]
(   
    @SkinId INT,
    @CurrencyIdFrom char(3),
    @CurrencyIdTo char(3),
    @EndDate datetime
)
RETURNS @Result
TABLE
(
    Id INT,
    CurrencyId char(3),
    ToCurrencyId char(3),
    Rate decimal(24,18),
    Date date
)
AS
Begin
    If (not exists( Select TOP 1 Id, CurrencyId,ToCurrencyId ,Rate , Date date
        From BCF.SkinCurrencyRate
        Where SkinId = @SkinId and CurrencyId = @CurrencyIdFrom AND ToCurrencyId = ISNULL(@CurrencyIdTo, 'EUR')  and Date<=@EndDate
        Order By Date desc))
    BEGIN
        INSERT INTO @Result 
        Select Id, CurrencyId,ToCurrencyId ,Rate , Date date from [BCF].fn_GetLastCurrencyRateByDate(@CurrencyIdFrom, @CurrencyIdTo, @EndDate)

    END
    ELSE
    BEGIN
        INSERT INTO @Result
        Select TOP 1 Id, CurrencyId,ToCurrencyId ,Rate , Date date
        From BCF.SkinCurrencyRate
        Where SkinId = @SkinId and CurrencyId = @CurrencyIdFrom AND ToCurrencyId = ISNULL(@CurrencyIdTo, 'EUR')  and Date<=@EndDate
        Order By Date desc
    END

    RETURN;
end
GO

Table Valued Function with Recursive CTE

The solution is to use a multi-statement Table Valued Function:

DROP FUNCTION IF EXISTS dates;
GO
CREATE FUNCTION dates(@start date, @end date) RETURNS @dates TABLE(date date) AS
BEGIN
    WITH cte AS (
        SELECT @start AS date
        UNION ALL
        SELECT dateadd(day,1,date) FROM cte WHERE date<@end
    )
    INSERT INTO @dates(date)
    SELECT date FROM cte OPTION(MAXRECURSION 0)
    RETURN;
END;
GO
SELECT * FROM dates('2020-01-01','2021-01-01');

Inline Table Valued Functions are literally inlined, and clauses such as OPTION can only appear at the very end of an SQL statement, which is not necessarily at the end of the inline function.

On the other hand, a Multi Statement Table Valued function is truly self-contained, so the OPTION clause is OK there.

---

Related Topics