通过

标量 UDF 内联

适用于：SQL Server 2019 (15.x) Azure SQL 数据库Azure SQL 托管实例Microsoft Fabric 中的 SQL 分析终结点Microsoft Fabric 中的仓库

本文介绍标量 UDF 内联，这是 SQL 数据库智能查询处理功能套件下的一项功能。 此功能可提高 SQL Server 2019 (15.x) 及更高版本中调用标量 UDF 的查询的性能。

T-SQL 标量用户定义函数

在 Transact-SQL 中实现并返回单个数据值的用户定义函数称为 T-SQL 标量用户定义函数 (UDF)。 T-SQL 的 UDF 是所有 Transact-SQL 查询实现代码重用和模块化的简洁方法。 某些计算（如复杂业务规则）更容易以命令式 UDF 形式表达。 UDF 有助于构建复杂逻辑，而无需具备编写复杂 SQL 查询的专业知识。 有关 UDF 的详细信息，请参阅创建用户定义函数（数据库引擎）。

标量 UDF 的性能

标量 UDF 通常表现不佳，原因如下：

  • 迭代调用。 UDF 以迭代方式调用，每个符合条件的元组调用一次。 由于函数调用，这会产生重复上下文切换的额外成本。 尤其是在其定义中执行 Transact-SQL 查询的 UDF 会受到严重影响。

  • 缺乏成本计算。 在优化期间，只有关系运算符会计算成本，标量运算符则不计算成本。 在引入标量 UDF 之前，其他标量运算符通常成本较低，不需要计算成本。 为标量操作添加的少量 CPU 成本就足够了。 在某些情况下，实际成本很高，但仍然被低估。

  • 解释型执行。 UDF 作为一批语句计算，逐条语句执行。 每条语句本身都会被编译，编译后的计划会被缓存。 尽管这种缓存策略避免了重新编译，从而节省了一些时间，但每条语句都是独立执行的。 不会执行跨语句优化。

  • 串行执行。 SQL Server 不允许在调用 UDF 的查询中进行查询内并行操作。

标量 UDF 的自动内联

标量 UDF 内联功能的目标是提高调用 T-SQL 标量 UDF 的查询的性能，其中 UDF 执行是主要瓶颈。

借助此新功能，标量 UDF 会自动转换为标量表达式或标量子查询，这些表达式或子查询会在调用查询中替代 UDF 运算符。 然后优化这些表达式和子查询。 结果是，查询计划不再包含用户定义函数运算符，但其效果会在计划中体现，如视图或内联表值函数 (TVF)。

Microsoft Fabric 数据仓库中标量 UDF 的自动内联

在 Microsoft Fabric 数据仓库中，标量 UDF（当前为预览版）在函数主体和调用查询满足内联要求时，会在编译时自动内联。 有关详细信息，请参阅 CREATE FUNCTION 和标量 UDF 内联。

示例

本部分中的示例使用 TPC-H 基准数据库。 有关详细信息，请参阅 TPC-H 主页。

答： 单语句标量 UDF

考虑以下查询。

SELECT L_SHIPDATE,
       O_SHIPPRIORITY,
       SUM(L_EXTENDEDPRICE * (1 - L_DISCOUNT))
FROM LINEITEM
     INNER JOIN ORDERS
         ON O_ORDERKEY = L_ORDERKEY
GROUP BY L_SHIPDATE, O_SHIPPRIORITY
ORDER BY L_SHIPDATE;

此查询计算行项目的折扣价格总和，并按发货日期和发货优先级分组显示结果。 表达式 L_EXTENDEDPRICE *(1 - L_DISCOUNT) 是给定行项目的折扣价格公式。 此类公式可以提取到函数中，以利于模块化和重用。

CREATE FUNCTION dbo.discount_price
(
    @price DECIMAL (12, 2),
    @discount DECIMAL (12, 2)
)
RETURNS DECIMAL (12, 2)
AS
BEGIN
    RETURN @price * (1 - @discount);
END

现在可以修改查询以调用此 UDF。

SELECT L_SHIPDATE,
       O_SHIPPRIORITY,
       SUM(dbo.discount_price(L_EXTENDEDPRICE, L_DISCOUNT))
FROM LINEITEM
     INNER JOIN ORDERS
         ON O_ORDERKEY = L_ORDERKEY
GROUP BY L_SHIPDATE, O_SHIPPRIORITY
ORDER BY L_SHIPDATE;

由于前面概述的原因，带有 UDF 的查询性能不佳。 使用标量 UDF 内联，UDF 主体中的标量表达式会直接替换到查询中。 运行此查询的结果如下表所示：

查询： 不含 UDF 的查询 含 UDF 的查询（无内联） 含标量 UDF 内联的查询
Execution time: 1.6 秒 29 分 11 秒 1.6 秒

这些数字基于在具有双处理器（12 核）、96 GB RAM 并由 SSD 支持的计算机上运行的 10 GB CCI 数据库（使用 TPC-H 架构）。 这些数字包括冷过程缓存和缓冲池的编译和执行时间。 使用默认配置，未创建其他索引。

B. 多语句标量 UDF

使用多个 T-SQL 语句（如变量赋值和条件分支）实现的标量 UDF 也可以内联。 考虑以下标量 UDF，给定客户键，确定该客户的服务类别。 它首先使用 SQL 查询计算客户下的所有订单的总价格，从而得出类别。 然后，它使用 IF (...) ELSE 逻辑根据总价格决定类别。

CREATE OR ALTER FUNCTION dbo.customer_category (@ckey INT)
RETURNS CHAR (10)
AS
BEGIN
    DECLARE @total_price AS DECIMAL (18, 2);
    DECLARE @category AS CHAR (10);
    SELECT @total_price = SUM(O_TOTALPRICE)
    FROM ORDERS
    WHERE O_CUSTKEY = @ckey;
    IF @total_price < 500000
        SET @category = 'REGULAR';
    ELSE
        IF @total_price < 1000000
            SET @category = 'GOLD';
        ELSE
            SET @category = 'PLATINUM';
    RETURN @category;
END

现在，考虑调用此 UDF 的查询。

SELECT C_NAME,
       dbo.customer_category(C_CUSTKEY)
FROM CUSTOMER;

SQL Server 2017 (14.x)（兼容级别 140 及更早版本）中此查询的执行计划如下：

无内联的查询计划的屏幕截图。

如计划所示，SQL Server 在此采用简单策略：对于 CUSTOMER 表中的每个元组，调用 UDF 并输出结果。 此策略简单但效率低下。 通过内联，此类 UDF 会转换为等效的标量子查询，这些子查询会在调用查询中替代 UDF。

对于同一查询，内联 UDF 的计划如下所示。

含内联的查询计划的屏幕截图。

如前所述，查询计划不再包含用户定义函数运算符，但其效果现在在计划中可见，如视图或内联 TVF。 以下是从前一计划中得出的一些关键观察结果：

  • SQL Server 推断 CUSTOMER 和 ORDERS 之间的隐式联接，并通过联接运算符将其显式化。

  • SQL Server 还推断隐式 GROUP BY O_CUSTKEY on ORDERS，并使用 IndexSpool + StreamAggregate 来实现它。

  • SQL Server 现在在所有运算符上使用并行处理。

根据 UDF 中逻辑的复杂性，生成的查询计划也可能变得更大、更复杂。 正如我们所看到的，UDF 内部的操作现在不再不透明，因此查询优化器能够对这些操作进行成本计算和优化。 此外，由于计划中不再包含 UDF，迭代 UDF 调用被完全避免函数调用开销的计划所取代。

可内联标量 UDF 要求

如果函数定义使用允许的构造，并且函数在启用内联的上下文中使用，则标量 T-SQL UDF 可以内联：

UDF 定义的所有以下条件必须为 true：

  • UDF 使用以下构造编写：
    • DECLARE、SET：变量声明和赋值。
    • SELECT带有单个/多个变量赋值的 SQL 查询 1。
    • IF / ELSE：具有任意嵌套级别的分支。
    • RETURN：单个或多个返回语句。 从 SQL Server 2019 (15.x) CU5 开始，UDF 只能包含一个被视为用于内联的 RETURN 语句6。
    • UDF：嵌套/递归函数调用 2。
    • 其他：关系操作，如 EXISTS、IS NULL。
  • UDF 不会调用任何与时间相关的内部函数（例如 GETDATE()）或具有副作用的函数3（例如 NEWSEQUENTIALID()）。
  • UDF 使用 EXECUTE AS CALLER 子句（如果未指定 EXECUTE AS 子句，则为默认行为）。
  • UDF 不引用表变量或表值参数。
  • UDF 不是本机编译的（支持互操作）。
  • UDF 不引用用户定义类型。
  • UDF 未添加签名 9。
  • UDF 不是配分函数。
  • UDF 不包含对公用表表达式 (CTE) 的引用。
  • UDF 不包含对内联时可能更改结果的内置函数的引用（如 @@ROWCOUNT）4。
  • UDF 不包含作为参数传递给标量 UDF 的聚合函数4。
  • UDF 不会引用内置视图（如 OBJECT_ID）4。
  • UDF 不引用 XML 方法5。
  • UDF 不包含 SELECT（带 ORDER BY，不带TOP 1 子句）5。
  • UDF 不包含与 ORDER BY 子句（如 SELECT @x = @x + 1 FROM table1 ORDER BY col1）一起用于执行赋值的 SELECT 查询5。
  • UDF 不包含多个 RETURN 语句6。
  • UDF 不引用 STRING_AGG 函数6。
  • UDF 不引用远程表 7。
  • UDF 不引用加密列8。
  • UDF 不包含对 WITH XMLNAMESPACES 的引用8。
  • 如果 UDF 定义包含数千行代码，SQL Server 可能会选择不内联它。

1SELECT 不支持带有变量累积/聚合的内联（如 SELECT @val += col1 FROM table1）。

2 递归 UDF 仅内联到一定深度。

3 结果取决于当前系统时间的内置函数是时间相关的。 可能更新某些内部全局状态的内置函数是具有副作用的函数的示例。 每次调用此类函数都会根据内部状态返回不同的结果。

4 在 SQL Server 2019 (15.x) CU 2 中添加的限制

5 在 SQL Server 2019 (15.x) CU 4 中添加的限制

6 在 SQL Server 2019 (15.x) CU 5 中添加的限制

7 在 SQL Server 2019 (15.x) CU 6 中添加的限制

8 在 SQL Server 2019 (15.x) CU 11 中添加的限制

9 因为可以在创建 UDF 后添加和删除签名，所以是否内联的决定在引用标量 UDF 的查询编译时做出。 例如，系统函数通常使用证书签名。 可以使用 sys.crypt_properties 查找签名的对象。

执行上下文的所有以下要求必须为 true：

  • ORDER BY 子句中未使用 UDF。
  • 调用标量 UDF 的查询不会在其 GROUP BY 子句中引用标量 UDF 调用。
  • 在带有 DISTINCT 子句的选择列表中调用标量 UDF 的查询没有 ORDER BY 子句。
  • 不会从 RETURN 语句调用 UDF1。
  • 调用 UDF 的查询没有公用表表达式 (CTE) 3。
  • UDF 调用查询不使用 GROUPING SETS、CUBE 或 ROLLUP2。
  • UDF 调用查询不包含用作赋值的 UDF 参数的变量（例如 SELECT @y = 2、@x = UDF(@y)）2。
  • UDF 不用于计算列或检查约束定义。

1 在 SQL Server 2019 (15.x) CU 5 中添加的限制

2 在 SQL Server 2019 (15.x) CU 6 中添加的限制

3 在 SQL Server 2019 (15.x) CU 11 中添加的限制

有关最新的 T-SQL 标量 UDF 内联修复和内联资格场景更改的信息，请参阅知识库文章：修复：SQL Server 2019 中的标量 UDF 内联问题。

检查 UDF 是否可以内联

对于每个 T-SQL 标量 UDF，sys.sql_modules 目录视图包含一个名为 is_inlineable 的属性，该属性指示 UDF 是否可内联。

is_inlineable 属性从 UDF 定义内的构造派生。 它不会在编译时检查 UDF 是否确实可内联。 有关详细信息，请参阅内联条件。

值为 1 表示 UDF 可内联，0 表示不可内联。 所有内联 TVF 的此属性值也为 1。 对于所有其他模块，值为 0。

如果标量 UDF 可内联，并不意味着它始终会被内联。 SQL Server（基于每个查询、每个 UDF）决定是否内联 UDF。 请参阅本文前面的要求列表。

SELECT b.name,
       b.type_desc,
       a.is_inlineable
FROM sys.sql_modules AS a
     INNER JOIN sys.objects AS b
         ON a.object_id = b.object_id
WHERE b.type IN ('IF', 'TF', 'FN');

检查是否已发生内联

如果满足所有前提条件且 SQL Server 决定执行内联，它会将 UDF 转换为关系表达式。 从查询计划中，可以确定是否发生了内联：

  • 计划 XML 中没有为成功内联的 UDF 包含 <UserDefinedFunction> XML 节点。
  • 发出某些扩展事件。

启用标量 UDF 内联

通过为数据库启用兼容级别 150，可以使工作负荷自动符合标量 UDF 内联的条件。 可以使用 Transact-SQL 设置此兼容级别。 例如：

ALTER DATABASE [WideWorldImportersDW]
    SET COMPATIBILITY_LEVEL = 150;

除了此步骤外，无需对 UDF 或查询进行其他更改即可利用此功能。

在不更改兼容级别的情况下禁用标量 UDF 内联

在保持数据库兼容级别 150 及更高的同时，可以在数据库、语句或 UDF 范围禁用标量 UDF 内联。 要在数据库范围禁用标量 UDF 内联，请在适用数据库的上下文中执行以下语句：

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

要为数据库重新启用标量 UDF 内联，请在适用数据库的上下文中执行以下语句：

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = ON;

当 ON 时，此设置在 sys.database_scoped_configurations 中显示为已启用。

还可以通过将 DISABLE_TSQL_SCALAR_UDF_INLINING 指定为 USE HINT 查询提示，为特定查询禁用标量 UDF 内联。

USE HINT 查询提示优先于数据库范围配置或兼容级别设置。

例如：

SELECT L_SHIPDATE,
       O_SHIPPRIORITY,
       SUM(dbo.discount_price(L_EXTENDEDPRICE, L_DISCOUNT))
FROM LINEITEM
     INNER JOIN ORDERS
         ON O_ORDERKEY = L_ORDERKEY
GROUP BY L_SHIPDATE, O_SHIPPRIORITY
ORDER BY L_SHIPDATE
OPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING'));

还可以使用 CREATE FUNCTION 或 ALTER FUNCTION 语句中的 INLINE 子句为特定 UDF 禁用标量 UDF 内联。 例如：

CREATE OR ALTER FUNCTION dbo.discount_price
(
    @price DECIMAL (12, 2),
    @discount DECIMAL (12, 2)
)
RETURNS DECIMAL (12, 2)
WITH INLINE = OFF
AS
BEGIN
    RETURN @price * (1 - @discount);
END

执行上述语句后，此 UDF 永远不会内联到调用它的任何查询中。 要为此 UDF 重新启用内联，请执行以下语句：

CREATE OR ALTER FUNCTION dbo.discount_price
(
    @price DECIMAL (12, 2),
    @discount DECIMAL (12, 2)
)
RETURNS DECIMAL (12, 2)
WITH INLINE = ON
AS
BEGIN
    RETURN @price * (1 - @discount);
END

INLINE 子句不是强制性的。 如果未指定 INLINE 子句，将根据 UDF 是否可以内联自动设置为 ON/OFF。 如果指定了 INLINE = ON 但发现 UDF 不符合内联条件，将引发错误。

注解

如本文所述，标量 UDF 内联将带有标量 UDF 的查询转换为带有等效标量子查询的查询。 由于此转换，在以下场景中可能会注意到行为上的一些差异：

  • 内联导致相同查询文本的查询哈希不同。

  • UDF 内语句中的某些警告（如除零等）以前可能被隐藏，由于内联可能会显示出来。

  • 查询级联接提示可能不再有效，因为内联可能会引入新的联接。 应改用本地联接提示。

  • 无法索引引用内联标量UDF的视图。 如果需要在此类视图上创建索引，请禁用对引用的 UDF 的内联。

  • 使用 UDF 内联时，动态数据屏蔽的行为可能会有一些差异。

    在某些情况下（取决于 UDF 中的逻辑），内联可能对于屏蔽输出列更为保守。 在 UDF 中引用的列不是输出列的场景中，这些列不会被屏蔽。

  • 如果 UDF 引用内置函数（如 SCOPE_IDENTITY()、@@ROWCOUNT 或 @@ERROR），则内联后内置函数返回的值会更改。 行为上的此更改是因为内联更改了 UDF 内语句的范围。 从 SQL Server 2019 (15.x) CU2 开始，如果 UDF 引用某些内部函数（例如 @@ROWCOUNT），则将阻止内联。

  • 如果将一个变量分配为内联 UDF 的结果，并且该变量也用作FORCESEEK查询提示（Transact-SQL）中index_column_name的一部分，则会导致错误 8622，指示查询处理器由于查询中定义的提示而无法生成查询计划。

相关内容