标量 UDF 内联

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

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

T-SQL 标量用户定义函数

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

注释

Microsoft Fabric Warehouse 和 SQL 分析端点使用 Expression Block 内联 和 Scalar UDF 内联来作为分布式查询的一部分执行符合条件的函数。 以下性能问题涉及在无内联情况下的执行。 后面展示的基准测试是 SQL Server 测量结果,而非 Microsoft Fabric 结果。 有关 Microsoft Fabric 中支持的功能和要求,请参见 CREATE FUNCTION。

标量 UDF 的性能

标量 UDF 通常表现不佳,原因如下:

  • 迭代调用。 SQL 数据库引擎以迭代方式调用 UDF,对每个符合条件的元组调用一次。 由于由于函数调用而重复的上下文切换,此过程会增加额外的成本。 在其定义中执行 Transact-SQL 查询的 UDF 受到严重影响。

  • 缺乏成本计算。 在优化过程中,数据库引擎只对关系运算符进行成本估算,而不对标量运算符进行成本估算。 在引入标量 UDF 之前,其他标量运算符通常成本较低,不需要计算成本。 为标量操作添加的少量 CPU 成本就足够了。 在某些情况下,实际成本很大,但优化器仍不代表它。

  • 解释型执行。 数据库引擎将 UDF 作为一批语句来求值,并逐条执行这些语句。 将编译每个语句,并缓存已编译的计划。 尽管此缓存策略通过避免重新编译来节省一些时间,但每个语句都以隔离方式执行。 数据库引擎不执行跨语句优化。

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

标量 UDF 的自动内联

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

通过使用 UDF 内联功能,数据库引擎会自动将标量 UDF 转换为标量表达式或标量子查询。 数据库引擎在调用查询中替换这些表达式或子查询,代替 UDF 运算符。 然后,查询优化器优化这些表达式和子查询。 因此,查询计划不再具有用户定义的函数运算符,但你可以在计划中观察其效果,如视图或内联表值函数(TVF)。

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

注释

Microsoft Fabric 中的 Warehouse 和 SQL 分析端点支持两种标量 UDF 内联技术:标量 UDF 内联和表达式块内联。 Fabric Data Warehouse引擎根据函数定义选择适用的技术。 这些技术共同支持更广泛的函数定义和查询形式。 例如,表达式块内联支持 WHILE 循环和多个 RETURN 语句等过程式构造,并支持时间相关的内置对象等。

当函数体和调用查询都满足内联要求时,合格的标量UDF可以在查询编译过程中自动内联。 选择内联技术无需使用任何语法。

默认情况下,CREATE OR ALTER FUNCTION 要求函数体满足内联要求。 要创建有意不可内联的函数,请指定 WITH INLINE = AUTO 函数选项。 这个选项允许创建该函数,但不会使该函数可内联。

关于 Fabric Data Warehouse 特定需求和受支持的结构,请参见 CREATE FUNCTION 和 标量 UDF 的内联。 有关支持的使用示例以及函数定义或调用查询的更改,请参阅在Microsoft Fabric Data Warehouse中创建标量用户定义函数。

示例

本部分中的示例使用 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 主体中的标量表达式会直接替换到查询中。 运行此查询的结果如下表所示:

Query 不含 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 查找签名的对象。

执行上下文的以下所有要求都必须成立:

  • 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 内联修复和内联资格方案的更改的信息,请参阅知识库文章:修复:2019 SQL Server中的标量 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 节点。
  • 发出某些扩展事件。

注释

在 Microsoft Fabric 中的仓库和 SQL 分析端点中,默认启用了内联功能。 以下章节中的兼容性级别、数据库范围配置、查询提示或INLINE = ONINLINE = OFF示例并非这些项目的配置合同。 有关支持的功能选项,请参见 CREATE FUNCTION 。

启用标量 UDF 内联

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

ALTER DATABASE [WideWorldImportersDW]
    SET COMPATIBILITY_LEVEL = 150;

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

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

可以在数据库、语句或 UDF 范围内禁用标量 UDF 内联,同时仍保持数据库兼容性级别 150 及更高。 若要在数据库范围内禁用标量 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 的结果,并且还在 index_column_nameFORCESEEK 中将其用作 ,则会导致错误 8622。 此错误表示查询处理器由于查询中定义的提示而无法生成查询计划。