sql - 多语句表值函数与内联表值函数

Question

举几个例子，以防万一:

内联表值

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

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

阅读有关它们以及优点/差异的信息还没有真正得到解释。

Answer 1

在研究马特的评论时，我修改了我的原始声明。他是对的，内联表值函数 (ITVF) 和多语句表值函数 (MSTVF) 之间的性能会有差异，即使它们都只是执行 SELECT 语句。 SQL Server 会像对待 VIEW 一样对待 ITVF。因为它将使用有关表的最新统计信息计算执行计划。 MSTVF 相当于将 SELECT 语句的全部内容填充到表变量中，然后加入该变量。因此，编译器不能对 MSTVF 中的表使用任何表统计信息。因此，在所有条件相同的情况下(它们很少如此)，ITVF 的性能将优于 MSTVF。在我的测试中，完成时间的性能差异可以忽略不计，但从统计的角度来看，这是显而易见的。

在您的情况下，这两个功能在功能上并不等效。 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 所做的一项出色的分析，比较了内联表值函数和多语句函数之间的性能差异。

His original blog post.

Copy on SQL Server Central