ユーザー定義関数

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceMicrosoft Fabric の SQL 分析エンドポイントMicrosoft Fabric のウェアハウスMicrosoft Fabric の SQL データベース

プログラミング言語の関数と同様、SQL Server のユーザー定義関数は、パラメーターを受け取り複雑な計算などの処理を実行してその結果を値として返すルーチンです。 戻り値は、単一のスカラー値または結果セットになります。

ユーザー定義関数の利点

ユーザー定義関数 (UDF) を使用する理由とは?

  • モジュール型プログラミング。 関数を作成してからデータベースに保存すると、プログラムの中で何度でも呼び出せます。 ユーザー定義関数は、プログラムのソース コードとは切り離して変更できます。

  • より高速な実行。 Transact-SQL のユーザー定義関数を使うと、ストアド プロシージャと同様に、プランがキャッシュされ、これを再利用して繰り返し実行することで、Transact-SQL のコードのコンパイル コストを削減できます。 つまり、ユーザー定義関数を各使用で再解析して再最適化する必要がないため、実行時間が短縮されます。

    計算タスク、文字列の操作、ビジネス ロジックの場合、共通言語ランタイム (CLR) 関数を使うと、Transact-SQL 関数よりパフォーマンスが大幅に向上します。 Transact-SQL 関数は、データ アクセスの多いロジックに適しています。

  • ネットワーク トラフィックの削減。 1 つのスカラー式では表現できないいくつかの複雑な制約に基づいてデータをフィルター処理する操作を、1 つの関数として表現できます。 その後、WHERE 句でこの関数を呼び出して、クライアントに送信される行の数を減らすことができます。

重要

クエリの Transact-SQL UDF は、1 つのスレッドでのみ実行できます (直列実行プラン)。 そのため、UDF を使用すると、並列クエリ処理が禁止されます。 並列クエリ処理の詳細については、「クエリ処理アーキテクチャ ガイド」をご覧ください。

関数の種類

このセクションでは、スカラー関数、テーブル値関数およびシステム関数の違いについて説明します。

スカラー関数

ユーザー定義のスカラー関数は、RETURNS 句で定義された型の単一のデータ値を返します。 インライン スカラー関数の場合、返されるスカラー値は単一ステートメントの結果です。 複数のステートメントがあるスカラー関数の場合、関数本体に、単一の値を返す一連の Transact-SQL ステートメントを含めることができます。 戻り値の型は、 text、 ntext、 image、 cursor、 timestampを除く各種のデータ型になります。 例については、「ユーザー定義関数を作成する (データベース エンジン)」をご覧ください。

テーブル値関数

ユーザー定義のテーブル値関数 (TVF) は、table データ型を返します。 インライン テーブル値関数の場合、テーブルは単一の SELECT ステートメントの結果セットであり、関数の本体がありません。 例については、「ユーザー定義関数を作成する (データベース エンジン)」をご覧ください。

システム関数

SQL Server には、さまざまな操作を実行するために使用できる多くのシステム関数が用意されています。 それらを変更することはできません。 詳細については、「SQL Database 関数とは」、「Transact-SQL のカテゴリ別のシステム関数」、および「システム動的管理ビュー」を参照してください。

ガイドライン

ステートメントが取り消されて、モジュール (トリガーやストアド プロシージャ) 内の次のステートメントが続行されるような Transact-SQL エラーについては、関数内では扱いが異なります。 関数内では、このようなエラーによって関数自体の実行が停止されます。 そのため、次に関数を呼び出したステートメントも取り消されることになります。

BEGIN...END ブロック内のステートメントには、副作用を持たせることはできません。 関数の副作用とは、データベース テーブルの変更など、その関数の有効範囲外のリソースの状態を永続的に変更してしまうことです。 関数内のステートメントが行える変更は、ローカル カーソルまたはローカル変数など、その関数に対してローカルなオブジェクトの変更のみです。 データベース テーブルへの変更、電子メールの送信、カタログの変更の試行、ユーザーに返される結果セットの生成など、関数に対してローカルではないカーソルに対する操作は、関数で実行できないアクションの例です。

CREATE FUNCTION ステートメントの発行時に存在しないリソースに対する副作用が CREATE FUNCTION ステートメントによって発生する場合は、SQL Server によってステートメントが実行されます。 ただし、SQL Server では関数は呼び出されても実行されません。

クエリで指定されている関数が実行される回数は、オプティマイザーによって作成される実行プランによって異なることがあります。 WHERE 句のサブクエリによって起動された関数がその一例です。 サブクエリとその関数が実行された回数は、オプティマイザーが選択したアクセス パスの違いによって変わります。

決定論的関数は、スキーマ バインドである必要があります。 決定論的関数を作成するときは、SCHEMABINDING 句を使います。

ユーザー定義関数の詳細とパフォーマンスに関する考慮事項については、「ユーザー定義関数を作成する (データベース エンジン)」をご覧ください。

関数で有効なステートメント

関数では、次の種類のステートメントが有効です。

  • 関数に対してローカルなデータ変数やカーソルを定義するために使用できる DECLARE ステートメント。

  • 関数に対してローカルなオブジェクトへの値の代入。たとえば、SET を使用してスカラー変数やテーブルのローカル変数に値を代入します。

  • ローカル カーソルを参照するカーソル操作。ローカル カーソルとは、関数内で宣言され、開かれ、閉じられ、割り当てが解除されるカーソルです。 クライアントにデータを返す FETCH ステートメントは使用できません。 FETCH 句を使用してローカル変数に値を代入する INTO ステートメントのみを使用できます。

  • TRY...CATCH ステートメントを除く、フロー制御ステートメント。

  • 選択リストに、関数に対してローカルな変数に値を代入する式が含まれる、SELECT ステートメント。

  • 関数に対してローカルなテーブル変数を変更する UPDATE、INSERT、DELETE の各ステートメント。

  • 拡張ストアド プロシージャを呼び出す EXECUTE ステートメント。

組み込みシステム関数

Transact-SQL ユーザー定義関数では、次の非決定的な組み込み関数を使用できます。

  • CURRENT_TIMESTAMP
  • GET_TRANSMISSION_STATUS
  • GETDATE
  • GETUTCDATE
  • @@CONNECTIONS
  • @@CPU_BUSY
  • @@DBTS
  • @@IDLE
  • @@IO_BUSY
  • @@MAX_CONNECTIONS
  • @@PACK_RECEIVED
  • @@PACK_SENT
  • @@PACKET_ERRORS
  • @@TIMETICKS
  • @@TOTAL_ERRORS
  • @@TOTAL_READ
  • @@TOTAL_WRITE

次の非決定的な組み込み関数 は、 Transact-SQL ユーザー定義関数 (UDF) では使用できません。

  • NEWID
  • NEWSEQUENTIALID
  • RAND
  • TEXTPTR

UDF 内でこれらの関数のいずれかを参照すると、次のエラーが発生します。

Msg 443, Level 16, State 1
Invalid use of a side-effecting operator <operator> within a function.

決定論的および非決定論的な組み込みシステム関数の一覧については、「決定論的関数と非決定論的関数」を参照してください。

スキーマ バインド関数

CREATE FUNCTION は、SCHEMABINDING 句をサポートしています。この句は、テーブル、ビュー、およびその他のユーザー定義関数など、参照対象オブジェクトのスキーマにその関数をバインドします。 スキーマ バインド関数によって参照されるオブジェクトを変更または削除しようとすると、失敗します。

SCHEMABINDING に を指定するには、次の条件を満たしている必要があります。

  • CREATE 関数が参照するすべてのビューとユーザー定義関数が、スキーマにバインドされている必要があります。

  • この関数が参照するすべてのオブジェクトが、その関数と同じデータベースに含まれている必要があります。 対象となるオブジェクトは、1 つまたは 2 つの要素で構成される名前を使用して参照する必要があります。

  • 関数内のすべての参照対象オブジェクト (テーブル、ビュー、ユーザー定義関数) に REFERENCES 権限を所持している必要があります。

ALTER FUNCTION を使用してスキーマ バインドを削除できます。 関数を再定義するには、ALTER FUNCTION ステートメントを使用します。WITH SCHEMABINDING は指定しないでください。

パラメーターの指定

ユーザー定義関数は、0 個またはそれ以上の入力パラメーターを受け取り、スカラー値またはテーブルのいずれかを返します。 1 つの関数では、最大で 1,024 個の入力パラメーターを受け取ることができます。 関数のパラメーターが既定値を持つ場合は、既定値を得るために、関数を呼び出すときに DEFAULT キーワードを指定する必要があります。 この動作はユーザー定義ストアド プロシージャ内の既定値を持つパラメーターとは異なります。ユーザー定義ストアド プロシージャの場合は、パラメーターを省略すると既定値が暗黙的に使用されます。 ユーザー定義関数では、出力パラメーターはサポートされません。

関連するコンテンツ

  • Last updated on