You may not know that there are two different ways of writing user-defined functions that return a recordset: Table value functions and Inline functions, and they both come with a number of benefits and limitations with regard to performance and programmability.

Table value function

A table value function is essentially a stored procedure that accepts arguments and returns a table recordset, defined as a table variable. The table value function can contain multiple statements and flow control logic, just like a regular stored procedure. The last statement of the procedure needs to be a RETURN statement, at which point the declared table variable is returned to the calling context.

Here’s an example of a table value function that returns a set of sales order rows, along with product data, for a specific sales order.

CREATE FUNCTION Sales.fn_OrderDetails_TF(@SalesOrderID int)
RETURNS @rows TABLE (
    OrderRow        bigint NOT NULL,
    OrderQty        smallint NOT NULL,
    UnitPrice       money NOT NULL,
    LineTotal       numeric(38, 6) NOT NULL,
    ProductName     nvarchar(50) NOT NULL,
    Color           nvarchar(30) NULL,
    StandardCost    money NOT NULL,
    [Weight]        decimal(8, 2) NULL,
    PRIMARY KEY CLUSTERED (OrderRow))
AS

BEGIN;

    INSERT INTO @rows (OrderRow, OrderQty, UnitPrice, LineTotal,
        ProductName, Color, StandardCost, [Weight])
    SELECT ROW_NUMBER() OVER (
            PARTITION BY detail.SalesOrderID
            ORDER BY SalesOrderDetailID) AS OrderRow,
        detail.OrderQty, detail.UnitPrice, detail.LineTotal,
        prod.[Name] AS ProductName, prod.Color,
        prod.StandardCost, prod.[Weight]
    FROM Sales.SalesOrderDetail AS detail
    INNER JOIN Production.Product AS prod ON
        detail.ProductID=prod.ProductID
    WHERE detail.SalesOrderID=@SalesOrderID;

    RETURN;
END;

GO

Inline function

If a table value function could be said to work like a stored procedure, an inline function is similar to a view. This means that an inline function can only contain a single SELECT statement, and the columns in the SELECT statement implicitly define the columns of the returned table set of the function.

CREATE FUNCTION Sales.fn_OrderDetails_IF(@SalesOrderID int)
RETURNS TABLE
AS

RETURN(
    SELECT ROW_NUMBER() OVER (
            PARTITION BY detail.SalesOrderID
            ORDER BY SalesOrderDetailID) AS OrderRow,
        detail.OrderQty, detail.UnitPrice, detail.LineTotal,
        prod.[Name] AS ProductName, prod.Color,
        prod.StandardCost, prod.[Weight]
    FROM Sales.SalesOrderDetail AS detail
    INNER JOIN Production.Product AS prod ON
        detail.ProductID=prod.ProductID
    WHERE detail.SalesOrderID=@SalesOrderID
);

GO

Performance comparison

The difference in how table value functions and inline functions work (comparable to a stored procedure and a view, respectively) affects how these two types of functions are evaluated and optimized in the query plan.

There’s no given when a table value function or an inline function is a better choice from a performance perspective – it depends on all those other factors in database performance tuning, such as indexing, data volumes, etc. However, there are clear performance differences between the two methods.

Here is a test query that selects all sales order rows for a given sales rep. The first statement uses the table value function we created earlier:

--- Table value function query
SELECT hdr.SalesOrderID, fn.*
FROM Sales.SalesOrderHeader AS hdr
CROSS APPLY Sales.fn_OrderDetails_TF(hdr.SalesOrderID) AS fn
WHERE hdr.SalesPersonID=279;

With the table value function, the execution happens in two steps: First, the return table is populated, after that, the returned table variable is joined with Sales.SalesOrderHeader.

But with an inline function, the function is “expanded” like a view, even though it is called on like a function using CROSS APPLY.

--- Inline table function query
SELECT hdr.SalesOrderID, fn.*
FROM Sales.SalesOrderHeader AS hdr
CROSS APPLY Sales.fn_OrderDetails_IF(hdr.SalesOrderID) AS fn
WHERE hdr.SalesPersonID=279;

How the function is expanded is clearly visible in the query plan:

In this case, the expanded view approach of the inline function carries a marked performance penalty.

For a fair comparison, we’ll also try to expand the query ourselves:

--- Regular query without functions
SELECT hdr.SalesOrderID, ROW_NUMBER() OVER (
        PARTITION BY detail.SalesOrderID
        ORDER BY SalesOrderDetailID) AS OrderRow,
    detail.OrderQty, detail.UnitPrice, detail.LineTotal,
    prod.[Name] AS ProductName, prod.Color,
    prod.StandardCost, prod.[Weight]
FROM Sales.SalesOrderHeader AS hdr
INNER JOIN Sales.SalesOrderDetail AS detail ON
    hdr.SalesOrderID=detail.SalesOrderID
INNER JOIN Production.Product AS prod ON
    detail.ProductID=prod.ProductID
WHERE hdr.SalesPersonID=279;

The query plan of this statement looks rather similar to that of the inline function plan:

Conclusion

Table value functions work like stored procedures, where the returned dataset is stored in a table variable (which can be indexed). On the other hand, inline functions are more similar to views, in that the query optimizer expands the function to be included in the query.

Performance will vary between the two approaches, but which method is the more efficient will depend on each individual situation.