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

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

使用一种类型（行内或多语句）是否比使用另一种类型更有优势？是否在某些情况下，一种方法比另一种更好，或者差异纯粹是语法上的？我意识到这两个示例查询正在执行不同的操作，但是我会以那种方式编写它们，是否有原因？

阅读它们和有关优点/差异的内容并没有真正被解释。

- AndrewC

---

另外，内联函数的一个巨大优势是您可以选择ROWID（TIMESTAMP）列，而在多语句函数中无法将TIMESTAMP数据插入返回表！ - Artur A

非常感谢这个精彩的帖子，我学到了很多。但是需要注意的一件事是，当我们将一个 ITV 函数 ALTER 为 MSTV 函数时，分析器会认为你正在修改 ITV 函数。无论从 MSTV 角度对语法做任何调整，重新编译总是失败的，通常是在 BEGIN 后的第一个语句附近。唯一的解决方法是删除旧函数，并创建一个新的 MSTV 函数。 - Fandango68

Answer 1

在研究Matt的评论后，我修改了我的原始声明。他是正确的，即使它们都只执行SELECT语句，内联表值函数（ITVF）和多语句表值函数（MSTVF）之间的性能差异仍然存在。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将获得更好的执行计划。根据我的经验，我发现很少有情况下ITVF比VIEW更好，但具体情况可能有所不同。

感谢Matt。

补充：

由于最近看到这个问题，这里有Wayne Sheffield进行的优秀分析，比较内联表值函数和多语句函数之间的性能差异。 他原始的博客文章。 SQL Server Central上的副本

- Thomas

---

这并不正确——多语句函数通常会极大地影响性能，因为它们会阻止查询优化器使用统计信息。如果我能获得每次看到多语句函数使用导致执行计划非常糟糕（主要是因为通常估计返回的行数为1）的1美元，我就有足够的钱买一辆小汽车了。 - Matt Whitfield

我找到的最好的解释在第一个答案和相关帖子中：https://dev59.com/Gm855IYBdhLWcg3w3IRh 不要错过相关文档，你可以快速阅读它，而且非常有趣。 - JotaBe

这个回答会更新到SQL Server 2017吗？：https://www.youtube.com/watch?time_continue=2&v=szTmo6rTUjM - Ralph

“但它仍然不会比等效的非参数化 ITVS 更好” - 这是否意味着参数的加入改变了它运行的方式？ - Murphybro2

Answer 2

在内部，SQL Server对内联表值函数的处理方式类似于视图，并将多语句表值函数处理类似于存储过程。

当内联表值函数作为外部查询的一部分时，查询处理器会展开UDF定义并生成执行计划，访问底层对象并使用这些对象上的索引。

对于多语句表值函数，将为函数本身创建执行计划，并将其存储在执行计划缓存中（一旦第一次执行该函数）。如果将多语句表值函数用作较大查询的一部分，则优化程序不知道函数的返回值，因此会做出一些标准假设 - 实际上它假设函数将返回单个行，并且通过针对具有单个行的表的表扫描来访问函数的返回。

多语句表值函数可能表现不佳的情况是当它们返回大量行并在外部查询中进行连接时。性能问题主要归因于优化程序会生成一个假定返回单个行的计划，这可能不是最适合的计划。

一般而言，我们发现在可能的情况下应首选使用内联表值函数而不是多语句函数（当UDF将用作外部查询的一部分时），以避免这些潜在的性能问题。

- Paul McLoughlin

---

虽然多语句表值函数可能与存储过程类似，但对于大型数据集而言，功能上相同的存储过程比表值函数快得多。我会坚持使用存储过程而不是多语句表值函数。 - Kekoa

除非你需要在另一个查询中连接这些结果。 - Guillermo Gutiérrez

为什么不两者兼顾呢？使用存储过程返回多语句表值函数的结果，最好两全其美。 - Robino

Answer 3

还有一个不同之处。内联表值函数可以像视图一样插入、更新和删除。类似的限制也适用于表值函数 - 不能使用聚合函数进行更新，不能更新计算列等。

- Craig Beere

Answer 4

我认为你的示例非常好地回答了问题。第一个函数可以作为单个选择完成，并且使用内联样式是一个很好的理由。第二个函数可能可以作为单个语句完成（使用子查询来获取最大日期），但是一些编程人员可能会发现按照你所做的那样分多个语句更容易阅读或更自然。有些函数根本无法在一个语句中完成，因此需要使用多语句版本。

我建议尽可能使用最简单的（内联）方式，并在必要时（显然）使用多语句，或者在个人喜好/可读性使其值得额外输入时使用多语句。

- Ray

---

谢谢您的回答。基本上，只有在函数比内联函数更复杂时，才真正需要使用多语句，为了提高可读性？多语句有任何性能优势吗？ - AndrewC

我不确定，但我认为不会。最好让SQL Server自己找出你可能尝试手动进行的优化（使用变量、临时表或其他方式）。虽然你可以在特定情况下进行一些性能测试来证明/否定这一点。 - Ray

非常感谢！我可能会在有更多时间的时候进一步研究这个！ :) - AndrewC

Answer 5

另一个使用多行函数的案例是为了避免SQL Server推动where子句。

例如，我有一个表格，其中包含一些表格名称格式为C05_2019和C12_2018，所有以这种方式格式化的表格具有相同的架构。 我想将所有数据合并到一个表中，并将05和12解析为CompNo列，将2018,2019解析为year列。 但是，还有其他表格，如ACA_StupidTable，我无法提取CompNo和CompYr，如果尝试会出现转换错误。 因此，我的查询分为两部分，内部查询仅返回格式为“C_______”的表格，然后外部查询执行子字符串和int转换。即Cast（Substring（2,2）as int）as CompNo。 看起来都很好，除了SQL Server决定在过滤结果之前放置我的Cast函数，因此我得到了令人头疼的转换错误。 多语句表函数可以防止发生这种情况，因为它基本上是一个“新”表。

- William Egge

Answer 6

也许可以简单地说一下。 ITVF（内联 TVF）：如果您是数据库人员，这种视图是一种参数化视图，只需要一个 SELECT 语句即可。 MTVF（多语句 TVF）：开发人员创建并加载表变量。

- LinchenPal

Answer 7

我没有测试过这个，但是一个多语句函数会缓存结果集。在优化器无法内联函数的情况下，可能会出现太多操作的情况。例如，假设您有一个函数，根据您传递的“公司编号”从不同的数据库返回结果。通常，您可以创建一个带有union all的视图，然后按公司编号进行过滤，但我发现有时sql server会拉回整个union，并不聪明地调用一个select。表函数可以具有选择源的逻辑。

- William Egge

Answer 8

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

- hmfarimani

Answer 9

如果你要进行查询，可以在内联表值函数中使用join语句：

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

因为每返回一行结果就会执行一次该函数，随着结果集的增大，它的运行速度会变得越来越慢。

- KM.

---

啊，那么你会说内联在性能方面更好？ - AndrewC

不，它们都返回一个表格，这使得你的第二个SQL无效，因为你试图将一个表格放入一列中。 - cjk

@ck，我已经更新了你评论的查询。第二个函数中使用的函数参数使它可以用作子查询，这将导致性能更差。 - KM.