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 将 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 更好，但效果可能会有所不同。

感谢马特。

加法

自从我最近看到这个问题以来，这是 Wayne Sheffield 所做的出色分析，比较了内联表值函数和多语句函数之间的性能差异。

His original blog post.

Copy on SQL Server Central