純量 UDF 內嵌

文章
10/31/2024

適用於：SQL Server 2019 (15.x) Azure SQL 資料庫 Azure SQL 受控執行個體

本文介紹純量 UDF 內嵌，這是 SQL Database 中處理智慧型查詢功能套件下的一項功能。此功能改善了在 SQL Server 2019 (15.x) 和最新版本中叫用純量 UDF 的查詢效能。

T-SQL 純量使用者定義的函式

以 Transact-SQL 實作並傳回單一資料值的使用者定義函式 (UDF)，就是所謂的 T-SQL 純量使用者定義函式。 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）。

範例

本章節中的範例會使用 TPC-H 基準檢驗資料庫。如需詳細資訊，請參閱 TPC-H 首頁。

A. 單一陳述式純量 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 內嵌的查詢
執行時間：	1.6 秒	29 分 11 秒	1.6 秒

這些數字是根據 10-GB CCI 資料庫 (使用 TPC-H 結構描述)，其在具有雙處理器 (12 個核心)、96 GB RAM 且 SSD 支援的機器上執行。這些數字包含冷程序快取和緩衝集區的編譯及執行時間。使用了預設設定，但未建立任何其他的索引。

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 會推斷和 ORDERS 之間的CUSTOMER隱含聯結，並透過聯結運算符進行明確。
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 查詢 ¹。
- IF/ELSE：使用任意巢狀層級的分支。
- RETURN:單一或多個傳回陳述式。從 SQL Server 2019 (15.x) CU5 開始，UDF 只能包含要考慮用於內嵌 ⁶ 的單一 RETURN 陳述式。
- UDF:巢狀/遞迴函式呼叫 ²。
- 其他：EXISTS、IS NULL 等關聯式作業。
UDF 不會叫用任何與時間相依 (例如 GETDATE()) 或有副作用 ³ (例如 NEWSEQUENTIALID()) 的內建函式。
UDF 會使用 EXECUTE AS CALLER 子句 (如果未指定 EXECUTE AS 子句，則為預設行為)。
UDF 不會參考資料表變數或資料表值參數。
不會以原生方式編譯 UDF (支援 Interop)。
UDF 不會參考使用者定義型別。
UDF ⁹ 中沒有新增簽章。
UDF 不是資料分割函式。
UDF 不包含對通用資料表運算式 (CTE) 的參考。
UDF 不包含內建函式的參考，這些函式可能會在內嵌時改變結果 (例如 @@ROWCOUNT)⁴。
UDF 不包含當作參數傳遞至純量 UDF 的彙總函式 ⁴。
UDF 不會參考內建檢視 (例如 OBJECT_ID)⁴。
UDF 不會參考 XML 方法 ⁵。
UDF 不包含使用 ORDER BY 的 SELECT 而沒有 TOP 1 子句 ⁵。
UDF 不包含具有 ORDER BY 子句執行指派的 SELECT 查詢 (例如 SELECT @x = @x + 1 FROM table1 ORDER BY col1)⁵。
UDF 不包含多個 RETURN 陳述式 ⁶。
UDF 不會參考 STRING_AGG 函式 ⁶。
UDF 不會參考遠端資料表 ⁷。
UDF 不會參考加密的資料行 ⁸。
UDF 不包含 WITH XMLNAMESPACES ⁸ 的參考。
如果 UDF 定義達到數千行程式碼時，SQL Server 可能會選擇不要加以內嵌。

內嵌不支援具有變數累積/彙總的 ¹ SELECT (例如 SELECT @val += col1 FROM table1)。

² 遞迴 UDF 只能內嵌到特定深度。

³ 其結果取決於目前系統時間的內建函式具有時間相依性。可能會更新某個內部全域狀態之內建函式為具有副作用的函式範例。這類函式會在每次呼叫時，根據內部狀態傳回不同的結果。

⁴ SQL Server 2019 (15.x) CU 2 中新增的限制

⁵ SQL Server 2019 (15.x) CU 4 中新增的限制

⁶ SQL Server 2019 (15.x) CU 5 中新增的限制

⁷ SQL Server 2019 (15.x) CU 6 中新增的限制

⁸ SQL Server 2019 (15.x) CU 11 中新增的限制

⁹ 因為可以在建立 UDF 之後新增和卸除簽章，因此決定是否要在參考純量 UDF 的查詢進行編譯時內嵌完成。例如，系統函式通常會以憑證簽署。您可以使用 sys. crypt_properties 尋找已簽署的物件。

執行內容的所有下列需求都必須成立：

UDF 不會在 ORDER BY 子句中使用。
叫用純量 UDF 的查詢不會在其 GROUP BY 子句中參考純量 UDF 呼叫。
在其選取清單中搭配 DISTINCT 子句叫用純量 UDF 的查詢不會有 ORDER BY 子句。
UDF 不是從 RETURN 語句 ¹ 呼叫。
叫用UDF的查詢沒有通用數據表運算式（CTE） ³。
UDF 呼叫查詢不會使用 GROUPING SETS、 CUBE或 ROLLUP ²。
UDF 呼叫查詢不包含用來作為指派 UDF 參數的變數（例如， SELECT @y = 2@x = UDF(@y)） ²。
UDF 不會用於計算資料行或檢查條件約束定義。

¹ SQL Server 2019 中新增的限制（15.x） CU 5

² SQL Server 2019 中新增的限制（15.x） CU 6

³ 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，以每個查詢為基礎。請參閱本文稍早的需求清單。

SELECT *
FROM sys.crypt_properties AS cp
     INNER JOIN sys.objects AS o
         ON cp.major_id = o.object_id;

檢查內嵌是否已發生

如果符合所有先決條件，且 SQL Server 決定執行內嵌，則它會將 UDF 轉換成關聯運算式。從查詢計劃中，您可以找出內嵌是否發生：

對於已成功內嵌的 UDF，計劃 XML 不會有 <UserDefinedFunction> XML 節點。
會發出特定擴充事件。

啟用純量 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_name FORCESEEK 查詢提示，則會產生錯誤 8622，表示查詢處理器因為查詢中定義的提示而無法產生查詢計劃。

分享方式：

純量 UDF 內嵌

T-SQL 純量使用者定義的函式

純量 UDF 的效能

自動內嵌純量 UDF

範例

A. 單一陳述式純量 UDF

B. 多重陳述式純量 UDF

可內嵌的純量 UDF 需求

檢查是否可以內嵌UDF

檢查內嵌是否已發生

啟用純量 UDF 內嵌

停用純量 UDF 內嵌而不變更相容性層級

備註

意見反映

更多資源

分享方式：

純量 UDF 內嵌

T-SQL 純量使用者定義的函式

純量 UDF 的效能

自動內嵌純量 UDF

範例

A. 單一陳述式純量 UDF

B. 多重陳述式純量 UDF

可內嵌的純量 UDF 需求

檢查是否可以內嵌UDF

檢查內嵌是否已發生

啟用純量 UDF 內嵌

停用純量 UDF 內嵌而不變更相容性層級

備註

相關內容

意見反映

更多資源