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

小编典典

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

all

举几个例子，以防万一：

内联表值

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

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

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

阅读 69

2022-06-28

共1个答案

小编典典

在研究马特的评论时，我修改了我原来的陈述。他是对的，内联表值函数 (ITVF) 和多语句表值函数 (MSTVF) 之间的性能会有差异，即使它们都只是执行
SELECT 语句。SQL Server 将 ITVF 视为有点像VIEW因为它将使用有关表的最新统计信息来计算执行计划。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 更好，但里程可能会有所不同。

2022-06-28