スカラー UDF のインライン化Scalar UDF Inlining

適用対象: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

この記事では、インテリジェントなクエリ処理機能スイートに含まれる機能であるスカラー UDF のインライン化について説明します。This article introduces Scalar UDF inlining, a feature under the intelligent query processing suite of features. この機能により、SQL ServerSQL Server (SQL Server 2019 (15.x)SQL Server 2019 (15.x) 以降) および SQL DatabaseSQL Database でスカラー UDF を呼び出すクエリのパフォーマンスが向上します。This feature improves the performance of queries that invoke scalar UDFs in SQL ServerSQL Server (starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x)) and SQL DatabaseSQL Database.

T-SQL スカラー ユーザー定義関数T-SQL Scalar User-Defined Functions

Transact-SQL で実装されていて単一のデータ値を返すユーザー定義関数は、T-SQL スカラー ユーザー定義関数と呼ばれます。User-Defined Functions that are implemented in Transact-SQL and return a single data value are referred to as T-SQL Scalar User-Defined Functions. T-SQL の UDF は、SQL クエリ間でコードの再利用とモジュール性を実現するための洗練された方法です。T-SQL UDFs are an elegant way to achieve code reuse and modularity across 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 を使用すると、通常、次の理由でパフォーマンスが低下します。Scalar UDFs typically end up performing poorly due to the following reasons.

  • 反復的な呼び出し: UDF は、該当するタプルごとに 1 回ずつ、反復的な方法で呼び出されます。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. 特に、定義内で SQL クエリを実行する UDF は大きな影響を受けます。Especially, UDFs that execute 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 Server では、UDF を呼び出すクエリでクエリ内の並列処理を行うことはできません。Serial execution: SQL Server does not allow intra-query parallelism in queries that invoke UDFs.

スカラー UDF の自動インライン化Automatic Inlining of Scalar UDFs

スカラー UDF インライン化機能の目的は、UDF の実行が主なボトルネックになる、T-SQL スカラー UDF を呼び出すクエリのパフォーマンスを向上させることです。The goal of the Scalar UDF inlining feature is to improve performance of queries that invoke T-SQL scalar UDFs, where UDF execution is the main bottleneck.

この新しい機能では、スカラー UDF はスカラー式またはスカラー サブクエリに自動的に変換され、呼び出し元のクエリ内で UDF 演算子の代わりに置き換えられます。With this new feature, scalar UDFs are automatically transformed into scalar expressions or scalar subqueries that are substituted in the calling query in place of the UDF operator. その後、これらの式とサブクエリは最適化されます。These expressions and subqueries are then optimized. その結果、クエリ プランにはユーザー定義関数演算子が含まれなくなりますが、ビューやインライン TVF などのように、その効果はプランに反映されます。As a result, the query plan will no longer have a user-defined function operator, but its effects will be observed in the plan, like views or inline TVFs.

例 1 - 単一ステートメントのスカラー UDFExample 1 - Single statement scalar UDF

次のようなクエリを検討しますConsider the following query


このクエリでは、明細品目の割引価格の合計が計算されて、出荷日および出荷優先度でグループ化された結果が表示されます。This query computes the sum of discounted prices for line items and presents the results grouped by the shipping date and shipping priority. L_EXTENDEDPRICE *(1 - L_DISCOUNT) は、特定の品目の割引価格の式です。The expression L_EXTENDEDPRICE *(1 - L_DISCOUNT) is the formula for the discounted price for a given line item. このような式は、モジュール化と再利用のために関数として抽出できます。Such formulas can be extracted into functions for the benefit of modularity and reuse.

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

そして、この UDF を呼び出すようにクエリを変更できます。Now the query can be modified to invoke this UDF.


先に説明した理由により、UDF を使用するクエリはパフォーマンスが低下します。Due to the reasons outlined earlier, the query with the UDF performs poorly. ここで、スカラー UDF のインライン化を使用すると、UDF の本体のスカラー式はクエリ内で直接置き換えられます。Now, with scalar UDF inlining, the scalar expression in the body of the UDF is substituted directly in the query. このクエリの実行結果は次の表のようになります。The results of running this query are shown in the below table:

クエリ:Query: UDF なしのクエリQuery without UDF UDF ありのクエリ (インライン化なし)Query with UDF (without inlining) スカラー UDF インライン化ありのクエリQuery with scalar UDF inlining
実行時間:Execution time: 1.6 秒1.6 seconds 29 分 11 秒29 minutes 11 seconds 1.6 秒1.6 seconds

これらの値は、10 GB の CCI データベース (TPC-H スキーマを使用) を使用し、デュアル プロセッサ (12 コア)、96 GB の RAM、SSD を備えたコンピューターで実行した場合のものです。These numbers are based on a 10-GB CCI database (using the TPC-H schema), running on a machine with dual processor (12 core), 96-GB RAM, backed by SSD. 値には、コールド プロシージャ キャッシュとバッファー プールを使用したコンパイルと実行の時間が含まれます。The numbers include compilation and execution time with a cold procedure cache and buffer pool. 既定の構成が使用され、他のインデックスは作成されませんでした。The default configuration was used, and no other indexes were created.

例 2 - 複数ステートメントのスカラー UDFExample 2 - Multi-statement scalar UDF

変数の代入や条件分岐など、複数の T-SQL ステートメントを使用して実装されるスカラー UDF もインライン展開できます。Scalar UDFs that are implemented using multiple T-SQL statements such as variable assignments and conditional branching can also be inlined. カスタマー キーを指定されて、その顧客のサービス カテゴリを決定する、次のようなスカラー UDF について考えます。Consider the following scalar UDF that, given a customer key, determines the service category for that customer. カテゴリを取得するには、最初に、SQL クエリを使用して、顧客による全注文の総額を計算します。It arrives at the category by first computing the total price of all orders placed by the customer using a SQL query. 次に、IF-ELSE ロジックを使用して、総額に基づいてカテゴリを決定します。Then, it uses an IF-ELSE logic to decide the category based on the total price.

CREATE OR ALTER FUNCTION dbo.customer_category(@ckey INT) 
  DECLARE @total_price DECIMAL(18,2);
  DECLARE @category CHAR(10);


  IF @total_price < 500000
    SET @category = 'REGULAR';
  ELSE IF @total_price < 1000000
    SET @category = 'GOLD';
    SET @category = 'PLATINUM';

  RETURN @category;

ここで、この UDF を呼び出すクエリを検討します。Now, consider a query that invokes this UDF.

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

SQL Server 2017 (互換性レベル 140 およびそれ以前) でのこのクエリの実行プランは次のようになります。The execution plan for this query in SQL Server 2017 (compatibility level 140 and earlier) is as follows:

インライン化のないクエリ プラン

プランで示されているように、ここでは SQL Server はシンプルな戦略を採用しています。CUSTOMER テーブル内のすべてのタプルについて、UDF を呼び出して結果を出力します。As the plan shows, SQL Server adopts a simple strategy here: for every tuple in the CUSTOMER table, invoke the UDF and output the results. この方法は単純で非効率的です。This strategy is naive and inefficient. インライン化を使用すると、このような UDF は同等のスカラー サブクエリに変換されて、呼び出し元のクエリで UDF の代わりに置き換えられます。With inlining, such UDFs are transformed into equivalent scalar subqueries, which are substituted in the calling query in place of the UDF.

同じクエリに対し、UDF のインライン化を使用したプランは次のようになります。For the same query, the plan with the UDF inlined looks as below.

インライン化のあるクエリ プラン

前に説明したように、クエリ プランにはユーザー定義関数演算子が含まれなくなりますが、ビューやインライン TVF などのように、その効果はプランにおいて確認できます。As mentioned earlier, the query plan no longer has a user-defined function operator, but its effects are now observable in the plan, like views or inline TVFs. 上のプランで確認できる重要な点をいくつか示します。Here are some key observations from the above plan:

  1. SQL Server により、CUSTOMERORDERS の間に暗黙の結合が推論され、それが結合演算子によって明示化されています。SQL Server has inferred the implicit join between CUSTOMER and ORDERS and made that explicit via a join operator.
  2. また、暗黙の GROUP BY O_CUSTKEY on ORDERS が推論され、IndexSpool と StreamAggregate を使用して実装されています。SQL Server has also inferred the implicit GROUP BY O_CUSTKEY on ORDERS and has used the IndexSpool + StreamAggregate to implement it.
  3. すべての演算子で並列処理が使用されるようになっています。SQL Server is now using parallelism across all operators.

UDF 内のロジックの複雑さによっては、結果として得られるクエリ プランがさらに大きくて複雑になる可能性があります。Depending upon the complexity of the logic in the UDF, the resulting query plan might also get bigger and more complex. ご覧のように、UDF の内部の演算がブラック ボックス化されなくなっており、そのため、クエリ オプティマイザーでコストを計算でき、これらの演算を最適化できます。As we can see, the operations inside the UDF are now no longer a black box, and hence the query optimizer is able to cost and optimize those operations. また、UDF がプランに含まれなくなったため、反復的な UDF の呼び出しは、関数呼び出しのオーバーヘッドがまったくないプランに置き換えられています。Also, since the UDF is no longer in the plan, iterative UDF invocation is replaced by a plan that completely avoids function call overhead.

インライン化可能なスカラー UDF の要件Inlineable Scalar UDFs requirements

以下のすべての条件に該当する場合、そのスカラー T-SQL UDF はインライン化できます。A scalar T-SQL UDF can be inline if all of the following conditions are true:

  • UDF が、次のコンストラクトを使用して書かれている。The UDF is written using the following constructs:
    • DECLARESET:変数の宣言と代入。DECLARE, SET: Variable declaration and assignments.
    • SELECT:単一/複数の変数代入を含む SQL クエリ1SELECT: SQL query with single/multiple variable assignments1.
    • IF/ELSE:任意の入れ子レベルでの分岐。IF/ELSE: Branching with arbitrary levels of nesting.
    • RETURN:1 つまたは複数の return ステートメント。RETURN: Single or multiple return statements.
    • UDF:入れ子/再帰関数呼び出し2UDF: Nested/recursive function calls2.
    • その他:EXISTSISNULL などの関係演算。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 がネイティブでコンパイルされていない (相互運用機能はサポートされます)。The UDF is not natively compiled (interop is supported).
  • UDF が、計算列または CHECK 制約定義で使用されていない。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.

UDF をインライン化できるかどうかの確認Checking whether or not a UDF can be inlined

すべての T-SQL スカラー UDF について、sys.sql_modules カタログ ビューに is_inlineable という名前のプロパティが含まれており、これは UDF がインライン化可能かどうかを示します。For every T-SQL scalar UDF, the sys.sql_modules catalog view includes a property called is_inlineable, which indicates whether a UDF is inlineable or not. 値 1 はインライン化可能であることを示し、0 はそれ以外を示します。A value of 1 indicates that it is inlineable, and 0 indicates otherwise. すべてのインライン TVF についても、このプロパティの値は 1 になります。This property will have a value of 1 for all inline TVFs as well. 他のすべてのモジュールでは、値は 0 になります。For all other modules, the value will be 0.


スカラー UDF がインライン化可能であっても、常にインライン化されるという意味ではありません。If a scalar UDF is inlineable, it does not imply that it will always be inlined. UDF をインライン化するかどうかは、SQL Server によって (クエリごと、UDF ごとに) 決定されます。SQL Server will decide (on a per-query, per-UDF basis) whether to inline a UDF or not. たとえば、UDF の定義が数千行のコードになる場合、SQL Server はインライン化しないことを選択する可能性があります。For instance, if the UDF definition runs into thousands of lines of code, SQL Server might choose not to inline it. もう 1 つの例として、GROUP BY 句内の UDF はインライン化されません。Another example is a UDF in a GROUP BY clause - which will not be inlined. この決定は、スカラー UDF を参照しているクエリのコンパイル時に行われます。This decision is made when the query referencing a scalar UDF is compiled.

インライン化が行われたかどうかの確認Checking whether inlining has happened or not

すべての前提条件が満たされていて、SQL Server がインライン化の実行を決定した場合、UDF は関係式に変換されます。If all the preconditions are satisfied and SQL Server decides to perform inlining, it transforms the UDF into a relational expression. インライン化が行われたかどうかは、クエリ プランを見ると簡単にわかります。From the query plan, it is easy to figure out 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

データベースに対して互換性レベル 150 を有効にすることで、自動的にワークロードをスカラー UDF インライン化の対象にすることができます。You can make workloads automatically eligible for scalar UDF inlining by enabling compatibility level 150 for the database.? これは Transact-SQL を使って設定できます。例を以下に示します。You can set this using Transact-SQL.?For example:


この機能を利用するために、UDF またはクエリに対して、これ以外に行う必要のある他の変更はありません。Apart from this, there are no other changes required to be made to UDFs or queries to take advantage of this feature.

互換性レベルを変更せずに、スカラー UDF のインライン化を無効にするDisabling Scalar UDF inlining without changing the compatibility level

スカラー UDF のインライン化は、データベースの互換性レベルを 150 以上に維持しながら、データベース、ステートメント、または UDF の範囲で、無効にすることができます。Scalar UDF inlining can be disabled at the database, statement, or UDF scope while still maintaining database compatibility level 150 and higher. データベースの範囲でスカラー UDF のインライン化を無効にするには、該当するデータベースのコンテキスト内で、次のステートメントを実行します。To disable scalar UDF inlining at the database scope, execute the following statement within the context of the applicable database:


データベースに対してスカラー UDF のインライン化を再び有効にするには、該当するデータベースのコンテキスト内で、次のステートメントを実行します。To re-enable scalar UDF inlining for the database, execute the following statement within the context of the applicable database:


ON のとき、この設定は sys.database_scoped_configurations で有効として表示されます。When ON, this setting will appear as enabled in sys.database_scoped_configurations. USE HINT クエリ ヒントとして DISABLE_TSQL_SCALAR_UDF_INLINING を指定することで、特定のクエリについてスカラー UDF のインライン化を無効にすることもできます。You can also disable scalar UDF inlining for a specific query by designating DISABLE_TSQL_SCALAR_UDF_INLINING as a USE HINT query hint. 例:For example:


USE HINT クエリ ヒントは、データベース スコープの構成または互換性レベルの設定より優先されます。A USE HINT query hint takes precedence over the database scoped configuration or compatibility level setting.

CREATE FUNCTION または ALTER FUNCTION ステートメントで INLINE 句を使用して、特定の UDF についてスカラー UDF のインライン化を無効にすることもできます。Scalar UDF inlining can also be disabled for a specific UDF using the INLINE clause in the CREATE FUNCTION or ALTER FUNCTION statement. 例:For example:

CREATE OR ALTER FUNCTION dbo.discount_price(@price DECIMAL(12,2), @discount DECIMAL(12,2))
    RETURN @price * (1 - @discount);

上のステートメントが実行されると、この UDF はそれを呼び出すクエリにインライン化されなくなります。Once the above statement is executed, this UDF will never be inlined into any query that invokes it. この UDF のインライン化を再度有効にするには、次のステートメントを実行します。To re-enable inlining for this UDF, execute the following statement:

CREATE OR ALTER FUNCTION dbo.discount_price(@price DECIMAL(12,2), @discount DECIMAL(12,2))
    RETURN @price * (1 - @discount);


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

この記事で説明したように、スカラー UDF のインライン化では、スカラー UDF を含むクエリが、同等のスカラー サブクエリを含むクエリに変換されます。As described in this article, scalar UDF inlining transforms a query with scalar UDFs into a query with an equivalent scalar subquery. この変換のため、次のようなシナリオでは、示される動作が異なる場合があります。Due to this transformation, users may notice some differences in behavior in the following scenarios:

  1. クエリ テキストが同じでも、インライン化が行われるとクエリ ハッシュが異なります。Inlining will result in a different query hash for the same query text.
  2. 以前は表示されなかった UDF 内のステートメントでの特定の警告 (0 による除算など) が、インライン化によって表示されるようになる場合があります。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() などの組み込み関数が参照されている場合、組み込み関数によって返される値はインライン化によって変化します。If a UDF references built-in functions such as SCOPE_IDENTITY(), 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.

参照See Also

SQL Server データベース エンジンと Azure SQL Database のパフォーマンス センターPerformance Center for SQL Server Database Engine and Azure SQL Database

クエリ処理アーキテクチャ ガイドQuery Processing Architecture Guide

プラン表示の論理操作と物理操作のリファレンスShowplan Logical and Physical Operators Reference


アダプティブ クエリ処理のデモンストレーションDemonstrating Adaptive Query Processing