多语句表值函数vs内联表值函数

下面举几个例子，以防万一:

内联表值

CREATE FUNCTION MyNS.GetUnshippedOrders()
RETURNS TABLE
AS 
RETURN SELECT a.SaleId, a.CustomerID, b.Qty
    FROM Sales.Sales a INNER JOIN Sales.SaleDetail b
        ON a.SaleId = b.SaleId
        INNER JOIN Production.Product c ON b.ProductID = c.ProductID
    WHERE a.ShipDate IS NULL
GO

多语句表取值

CREATE FUNCTION MyNS.GetLastShipped(@CustomerID INT)
RETURNS @CustomerOrder TABLE
(SaleOrderID    INT         NOT NULL,
CustomerID      INT         NOT NULL,
OrderDate       DATETIME    NOT NULL,
OrderQty        INT         NOT NULL)
AS
BEGIN
    DECLARE @MaxDate DATETIME

    SELECT @MaxDate = MAX(OrderDate)
    FROM Sales.SalesOrderHeader
    WHERE CustomerID = @CustomerID

    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 = @MaxDate
        AND a.CustomerID = @CustomerID
    RETURN
END
GO

使用一种类型(内联或多语句)是否比另一种有优势?是否存在一个比另一个更好的情况，或者这种差异纯粹是句法上的?我意识到这两个示例查询正在做不同的事情，但有一个原因我将以这种方式编写它们吗?

读到它们，它们的优点/区别并没有真正被解释清楚。

当前回答

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.

在你的例子中，这两个函数在函数上不相等。MSTV函数每次被调用时都执行一个额外的查询，最重要的是，它对客户id进行过滤。在大型查询中，优化器将无法利用其他类型的连接，因为它需要为传递的每个customerId调用函数。然而，如果你重写你的MSTV函数像这样:

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

在查询中，优化器可以调用该函数一次，并构建更好的执行计划，但它仍然不会比等效的、非参数化的ITVS或VIEW更好。

在可行的情况下，ITVF应该优先于mstvf，因为数据类型、可空性和排序规则来自表中的列，而您在多语句表值函数中声明这些属性，重要的是，您将从ITVF获得更好的执行计划。根据我的经验，我没有发现很多情况下，ITVF是一个比VIEW更好的选择，但里程可能会有所不同。

多亏了马特。

除了

因为我最近看到了这个问题，下面是Wayne Sheffield做的一个很好的分析，比较了内联表值函数和多语句函数之间的性能差异。

他的博客原文。

复制到SQL Server Central

2010-03-31 16:10:53

其他回答

如果你要做一个查询，你可以在你的内联表值函数中加入:

SELECT
    a.*,b.*
    FROM AAAA a
        INNER JOIN MyNS.GetUnshippedOrders() b ON a.z=b.z

它将产生很少的开销，并且运行良好。

如果你试图在类似的查询中使用你的多语句表值，你会有性能问题:

SELECT
    x.a,x.b,x.c,(SELECT OrderQty FROM MyNS.GetLastShipped(x.CustomerID)) AS Qty
    FROM xxxx   x

由于您将对返回的每一行执行函数1次，因此随着结果集变得越来越大，它将运行得越来越慢。

2010-03-31 15:48:03

我认为你的例子很好地回答了这个问题。第一个函数可以作为一个单独的选择来完成，这是使用内联样式的一个很好的理由。第二种方法可能可以作为一条语句来完成(使用子查询来获得最大日期)，但一些编码器可能会发现，像您这样在多条语句中完成它更容易阅读或更自然。有些函数不能在一条语句中完成，因此需要多语句版本。

我建议尽可能使用最简单的(内联)，并在必要时(显然)或当个人偏好/可读性使它与额外的类型时使用多语句。

2010-03-31 15:47:57

看看比较内联和多语句表值函数，你可以找到很好的描述和性能基准

2015-02-17 09:17:03

也许是以一种非常简洁的方式。 ITVF(内联TVF):如果你是DB人，是一种参数化的视图，取一个SELECT st

MTVF(多语句TVF):开发人员，创建并加载表变量。

2020-02-18 10:01:27

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