# 純量 UDF 內嵌Scalar UDF Inlining

## T-SQL 純量使用者定義函式T-SQL scalar User-Defined Functions

Transact-SQLTransact-SQL 實作並傳回單一資料值的使用者定義函式 (UDF)，就是所謂的 T-SQL 純量使用者定義函式。User-Defined Functions (UDFs) that are implemented in Transact-SQLTransact-SQL and return a single data value are referred to as T-SQL Scalar User-Defined Functions. T-SQL UDF 是一種在 Transact-SQLTransact-SQL 查詢之間達成程式碼重複使用和模組化的優雅方式。T-SQL UDFs are an elegant way to achieve code reuse and modularity across Transact-SQLTransact-SQL queries. 某些計算 (例如複雜的商務規則) 更容易以命令式 UDF 格式表達。Some computations (such as complex business rules) are easier to express in imperative UDF form. UDF 有助於建置複雜的邏輯，而不需要撰寫複雜 SQL 查詢的專業知識。UDFs help in building up complex logic without requiring expertise in writing complex SQL queries.

## 純量 UDF 的效能Performance of scalar UDFs

• 反覆叫用： UDF 會以反覆方式叫用，每個合格的元組一次。Iterative invocation: UDFs are invoked in an iterative manner, once per qualifying tuple. 這會因函式叫用而產生重複內容切換的額外成本。This incurs additional costs of repeated context switching due to function invocation. 特別是，在其定義中執行 Transact-SQLTransact-SQL 查詢的 UDF 會受到嚴重影響。Especially, UDFs that execute Transact-SQLTransact-SQL queries in their definition are severely affected.

• 缺少成本估算： 在最佳化期間，只會估算關係運算子的成本，而不會估算純量運算子的成本。Lack of costing: During optimization, only relational operators are costed, while scalar operators are not. 在引進純量 UDF 之前，其他純量運算子通常很便宜，而不需要進行成本估算。Prior to the introduction of scalar UDFs, other scalar operators were generally cheap and did not require costing. 為純量運算子新增少量 CPU 成本便已足夠。A small CPU cost added for a scalar operation was enough. 但還是有一些實際成本很高，卻仍然未充分表示的情況。There are scenarios where the actual cost is significant, and yet still remains underrepresented.

• 解譯執行： UDF 會評估為陳述式批次，逐一執行陳述式。Interpreted execution: UDFs are evaluated as a batch of statements, executed statement-by-statement. 系統會編譯每個陳述式本身，並快取已編譯的計劃。Each statement itself is compiled, and the compiled plan is cached. 雖然此快取策略可因避免重新編譯而節省一些時間，但每個陳述式都會單獨執行。Although this caching strategy saves some time as it avoids recompilations, each statement executes in isolation. 不會執行跨陳述式的最佳化。No cross-statement optimizations are carried out.

• 序列執行： SQL ServerSQL Server 不允許在叫用 UDF 的查詢中使用內部查詢平行處理原則。Serial execution: SQL ServerSQL Server does not allow intra-query parallelism in queries that invoke UDFs.

## 自動內嵌純量 UDFAutomatic inlining of scalar UDFs

### 範例 1 - 單一陳述式純量 UDFExample 1 - Single statement scalar 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;
``````

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

``````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
``````

### 範例 2 - 多重陳述式純量 UDFExample 2 - Multi-statement scalar UDF

``````CREATE OR ALTER FUNCTION dbo.customer_category(@ckey INT)
RETURNS CHAR(10) AS
BEGIN
DECLARE @total_price DECIMAL(18,2);
DECLARE @category 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
``````

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

SQL Server 2017 (14.x)SQL Server 2017 (14.x) (相容性層級 140 及更早版本) 中此查詢的執行計劃如下：The execution plan for this query in SQL Server 2017 (14.x)SQL Server 2017 (14.x) (compatibility level 140 and earlier) is as follows:

• SQL ServerSQL Server 已推斷 `CUSTOMER``ORDERS` 之間的隱含聯結，並透過聯結運算子使其變成明確聯結。has inferred the implicit join between `CUSTOMER` and `ORDERS` and made that explicit via a join operator.
• SQL ServerSQL Server 還會推斷隱含的 `GROUP BY O_CUSTKEY on ORDERS`，並使用 IndexSpool + StreamAggregate 來進行實作。has also inferred the implicit `GROUP BY O_CUSTKEY on ORDERS` and has used the IndexSpool + StreamAggregate to implement it.
• SQL ServerSQL Server 現在正在所有的運算子之間使用平行處理原則。is now using parallelism across all operators.

## 可內嵌的純量 UDF 需求Inlineable scalar UDFs requirements

• UDF 使用下列建構函式撰寫：The UDF is written using the following constructs:
• `DECLARE``SET`：變數宣告和指派。`DECLARE`, `SET`: Variable declaration and assignments.
• `SELECT`:包含單一/多個變數指派的 SQL 查詢1`SELECT`: SQL query with single/multiple variable assignments1.
• `IF`/`ELSE`：使用任意巢狀層級的分支。`IF`/`ELSE`: Branching with arbitrary levels of nesting.
• `RETURN`:單一或多個傳回陳述式。`RETURN`: Single or multiple return statements.
• `UDF`:巢狀/遞迴函式呼叫2`UDF`: Nested/recursive function calls2.
• 其他：`EXISTS``ISNULL` 等關聯式作業。Others: Relational operations such as `EXISTS`, `ISNULL`.
• UDF 不會叫用任何與時間相依 (例如 `GETDATE()`) 或有副作用3 (例如 `NEWSEQUENTIALID()`) 的內建函式。The UDF does not invoke any intrinsic function that is either time-dependent (such as `GETDATE()`) or has side effects3 (such as `NEWSEQUENTIALID()`).
• UDF 會使用 `EXECUTE AS CALLER` 子句 (如果未指定 `EXECUTE AS` 子句，則為預設行為)。The UDF uses the `EXECUTE AS CALLER` clause (the default behavior if the `EXECUTE AS` clause is not specified).
• UDF 不會參考資料表變數或資料表值參數。The UDF does not reference table variables or table-valued parameters.
• 叫用純量 UDF 的查詢不會在其 `GROUP BY` 子句中參考純量 UDF 呼叫。The query invoking a scalar UDF does not reference a scalar UDF call in its `GROUP BY` clause.
• 在其選取清單中搭配 `DISTINCT` 子句叫用純量 UDF 的查詢不會具備 `ORDER BY` 子句。The query invoking a scalar UDF in its select list with `DISTINCT` clause does not have `ORDER BY` clause.
• UDF 不會用在 `ORDER BY` 子句中。The UDF is not used in `ORDER BY` clause.
• 不會以原生方式編譯 UDF (支援 Interop)。The UDF is not natively compiled (interop is supported).
• UDF 不會用於計算資料行或檢查條件約束定義。The UDF is not used in a computed column or a check constraint definition.
• UDF 不會參考使用者定義型別。The UDF does not reference user-defined types.
• UDF 中沒有新增任何簽章。There are no signatures added to the UDF.
• UDF 不是資料分割函式。The UDF is not a partition function.

1 內嵌不支援具有變數累積/彙總的 `SELECT` (例如 `SELECT @val += col1 FROM table1`)。1 `SELECT` with variable accumulation/aggregation (for example, `SELECT @val += col1 FROM table1`) is not supported for inlining.

2 遞迴 UDF 只能內嵌到特定深度。2 Recursive UDFs will be inlined to a certain depth only.

3 其結果取決於目前系統時間的內建函式具有時間相依性。3 Intrinsic functions whose results depend upon the current system time are time-dependent. 可能會更新某個內部全域狀態之內建函式為具有副作用的函式範例。An intrinsic function that may update some internal global state is an example of a function with side effects. 這類函式會在每次呼叫時，根據內部狀態傳回不同的結果。Such functions return different results each time they are called, based on the internal state.

### 檢查是否可以內嵌 UDFChecking whether or not a UDF can be inlined

`is_inlineable` 屬性衍生自 UDF 定義內找到的建構。The `is_inlineable` property is derived from the constructs found inside the UDF definition. 它不會在編譯時間檢查 UDF 是否可實際內嵌。It does not check whether the UDF is in fact inlineable at compile time. 如需詳細資訊，請參閱內嵌條件For more information, see the conditions for inlining.

• 如果 UDF 定義達到數千行程式碼，SQL ServerSQL Server 可能會選擇不要加以內嵌。If the UDF definition runs into thousands of lines of code, SQL ServerSQL Server might choose not to inline it.

• 系統將不會內嵌 `GROUP BY` 子句中的 UDF 引動過程。A UDF invocation in a `GROUP BY` clause will not be inlined. 這項決定會在編譯參考純量 UDF 的查詢時進行。This decision is made when the query referencing a scalar UDF is compiled.

• 如果 UDF 是以憑證簽署。If the UDF is signed with a certificate. 因為可以在建立 UDF 之後加入並置放簽章，所以在編譯參考純量 UDF 的查詢時，會決定是否要內嵌。Because signatures could be added and dropped after a UDF has been created, the decision of whether to inline or not is done when the query referencing a scalar UDF is compiled. 例如，系統函式通常會以憑證簽署。For example, system functions are typically signed with a certificate. 您可以使用 sys. crypt_properties 尋找已簽署的物件。You can use sys.crypt_properties to find which objects are signed.

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

### 檢查是否已進行內嵌Checking whether inlining has happened or not

• 針對已成功內嵌的 UDF，計劃 XML不會有 `<UserDefinedFunction>` XML 節點。The plan xml will not have a `<UserDefinedFunction>` xml node for a UDF that has been inlined successfully.
• 會發出特定 XEvent。Certain XEvents are emitted.

## 啟用純量 UDF 內嵌Enabling Scalar UDF Inlining

``````ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 150;
``````

## 停用純量 UDF 內嵌而不變更相容性層級Disabling Scalar UDF Inlining without changing the compatibility level

``````ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;
``````

``````ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = ON;
``````

``````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'));
``````

`USE HINT` 查詢提示的優先順序高於資料庫範圍設定或相容性層級設定。A `USE HINT` query hint takes precedence over the database scoped configuration or compatibility level setting.

``````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;
``````

``````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` 子句非為強制性。The `INLINE` clause is not mandatory. 如果未指定 `INLINE` 子句，它會根據是否可以內嵌 UDF 自動設為 `ON`/`OFF`If `INLINE` clause is not specified, it is automatically set to `ON`/`OFF` based on whether the UDF can be inlined. 如果指定了 `INLINE = ON`，但發現 UDF 不適合進行內嵌，則會擲回錯誤。If `INLINE = ON` is specified but the UDF is found ineligible for inlining, an error will be thrown.

## 重要事項Important Notes

1. 內嵌會導致相同的查詢文字產生不同查詢雜湊。Inlining will result in a different query hash for the same query text.
2. UDF 內先前可能已隱藏的陳述式特定警告 (例如除以零等等)，可能會因內嵌而顯示出來。Certain warnings in statements inside the UDF (such as divide by zero etc.) which might have been hidden earlier, might show up due to inlining.
3. 查詢層級的聯結提示可能不再有效，因為內嵌可能會引進新的聯結。Query level join hints might not be valid anymore, as inlining may introduce new joins. 必須改為使用本機聯結提示。Local join hints will have to be used instead.
4. 參考內嵌純量 UDF 的檢視無法編製索引。Views that reference inline scalar UDFs cannot be indexed. 如果您需要在這類檢視上建立索引，請停用所參考 UDF 的內嵌功能。If you need to create an index on such views, disable inlining for the referenced UDFs.
5. 動態資料遮罩與內嵌 UDF 的行為可能有一些差異。There might be some differences in the behavior of Dynamic Data masking with UDF inlining. 在某些情況下 (視 UDF 中的邏輯而定)，對於遮罩輸出資料行，內嵌可能更為保守。In certain situations (depending upon the logic in the UDF), inlining might be more conservative w.r.t masking output columns. 在 UDF 中所參考資料行不是輸出資料行的情況下，它們不會被遮罩。In scenarios where the columns referenced in a UDF are not output columns, they will not be masked.
6. 如果 UDF 參考內建函式 (例如 `SCOPE_IDENTITY()``@@ROWCOUNT``@@ERROR`)，則內建函式所傳回的值會隨著內嵌而變更。If a UDF references built-in functions such as `SCOPE_IDENTITY()`, `@@ROWCOUNT`, or `@@ERROR`, the value returned by the built-in function will change with inlining. 此行為變更是因為內嵌變更了陳述式在 UDF 內的範圍。This change in behavior is because inlining changes the scope of statements inside the UDF.