CREATE FUNCTION (Transact-SQL)

[アーティクル]
04/26/2024

適用対象: SQL Server Azure SQL Database Azure SQL Managed Instance

Transact-SQL または共通言語ランタイム (CLR) ルーチンであるユーザー定義関数 (UDF) を作成します。ユーザー定義関数は、パラメーターを受け取り、複雑な計算などのアクションを実行し、そのアクションの結果を値として返します。戻り値は、スカラー (単一) 値またはテーブルにすることができます。このステートメントを使用して、次の方法で使用できる再利用可能なルーチンを作成します。

Transact-SQL ステートメントでは、次のような SELECT
関数を呼び出すアプリケーションの場合
別のユーザー定義関数の定義内で使用する
ビューをパラメーター化したり、インデックス付きビューの機能を向上させる
テーブルの列を定義する
列に CHECK 制約を定義するには
ストアドプロシージャを置換する
セキュリティポリシーのフィルター述語としてのインライン関数を使用します。

この記事では、SQL Server への .NET Framework CLR の統合について説明します。 CLR 統合は、Azure SQL Database には適用されません。

Azure Synapse Analytics または Microsoft Fabric の場合は、「CREATE FUNCTION (Azure Synapse Analytics および Microsoft Fabric)」を参照してください。

Transact-SQL 構文表記規則

構文

Transact-SQL スカラー関数の構文。

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
 [ = default ] [ READONLY ] }
    [ , ...n ]
  ]
)
RETURNS return_data_type
    [ WITH <function_option> [ , ...n ] ]
    [ AS ]
    BEGIN
        function_body
        RETURN scalar_expression
    END
[ ; ]

Transact-SQL インラインテーブル値関数の構文。

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
    [ = default ] [ READONLY ] }
    [ , ...n ]
  ]
)
RETURNS TABLE
    [ WITH <function_option> [ , ...n ] ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

Transact-SQL の複数ステートメントのテーブル値関数の構文。

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
    [ = default ] [ READONLY ] }
    [ , ...n ]
  ]
)
RETURNS @return_variable TABLE <table_type_definition>
    [ WITH <function_option> [ , ...n ] ]
    [ AS ]
    BEGIN
        function_body
        RETURN
    END
[ ; ]

Transact-SQL 関数句の構文。

<function_option> ::=
{
    [ ENCRYPTION ]
  | [ SCHEMABINDING ]
  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
  | [ EXECUTE_AS_Clause ]
  | [ INLINE = { ON | OFF } ]
}

<table_type_definition> ::=
( { <column_definition> <column_constraint>
  | <computed_column_definition> }
    [ <table_constraint> ] [ , ...n ]
)
<column_definition> ::=
{
    { column_name data_type }
    [ [ DEFAULT constant_expression ]
      [ COLLATE collation_name ] | [ ROWGUIDCOL ]
    ]
    | [ IDENTITY [ (seed , increment ) ] ]
    [ <column_constraint> [ ...n ] ]
}

<column_constraint> ::=
{
    [ NULL | NOT NULL ]
    { PRIMARY KEY | UNIQUE }
      [ CLUSTERED | NONCLUSTERED ]
      [ WITH FILLFACTOR = fillfactor
        | WITH ( <index_option> [ , ...n ] )
      [ ON { filegroup | "default" } ] ]
  | [ CHECK ( logical_expression ) ] [ , ...n ]
}

<computed_column_definition> ::=
column_name AS computed_column_expression

<table_constraint> ::=
{
    { PRIMARY KEY | UNIQUE }
      [ CLUSTERED | NONCLUSTERED ]
      ( column_name [ ASC | DESC ] [ , ...n ]
        [ WITH FILLFACTOR = fillfactor
        | WITH ( <index_option> [ , ...n ] )
  | [ CHECK ( logical_expression ) ] [ , ...n ]
}

<index_option> ::=
{
    PAD_INDEX = { ON | OFF }
  | FILLFACTOR = fillfactor
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
}

CLR スカラー関数の構文。

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
    [ = default ] }
    [ , ...n ]
)
RETURNS { return_data_type }
    [ WITH <clr_function_option> [ , ...n ] ]
    [ AS ] EXTERNAL NAME <method_specifier>
[ ; ]

CLR テーブル値関数の構文。

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
    [ = default ] }
    [ , ...n ]
)
RETURNS TABLE <clr_table_type_definition>
    [ WITH <clr_function_option> [ , ...n ] ]
    [ ORDER ( <order_clause> ) ]
    [ AS ] EXTERNAL NAME <method_specifier>
[ ; ]

CLR 関数句の構文。

<order_clause> ::=
{
   <column_name_in_clr_table_type_definition>
   [ ASC | DESC ]
} [ , ...n ]

<method_specifier> ::=
    assembly_name.class_name.method_name

<clr_function_option> ::=
{
    [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
  | [ EXECUTE_AS_Clause ]
}

<clr_table_type_definition> ::=
( { column_name data_type } [ , ...n ] )

ネイティブコンパイルのスカラーユーザー定義関数のインメモリ OLTP 構文。

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
 ( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type
    [ NULL | NOT NULL ] [ = default ] [ READONLY ] }
    [ , ...n ]
  ]
)
RETURNS return_data_type
     WITH <function_option> [ , ...n ]
    [ AS ]
    BEGIN ATOMIC WITH (set_option [ , ... n ] )
        function_body
        RETURN scalar_expression
    END

<function_option> ::=
{
  |  NATIVE_COMPILATION
  |  SCHEMABINDING
  | [ EXECUTE_AS_Clause ]
  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
}

引数

OR ALTER

適用対象: SQL Server 2016 (13.x) SP 1 以降のバージョン、および Azure SQL Database。

条件付きで、既に存在する場合にのみ関数を変更します。

省略可能な OR ALTER 構文は、SQL Server 2016 (13.x) SP 1 CU 1 以降の CLR で使用できます。

schema_name

ユーザー定義関数が属するスキーマの名前。

function_name

ユーザー定義関数の名前。関数名は、識別子のルールに従っている必要があります。また、データベース内、およびそのスキーマに対して一意である必要があります。

パラメーターが指定されていない場合でも、関数名の後にかっこが必要です。

@parameter_name

ユーザー定義関数のパラメーター。 1 つ以上のパラメーターを宣言できます。

1 つの関数では、最高 2,100 個のパラメーターを使用できます。宜言した各パラメーターの値は、関数の実行時に、ユーザーが指定する必要があります (そのパラメーターの既定値が定義されていない場合)。

最初の文字をアットマーク (@) にしてパラメーター名を指定します。パラメーター名は識別子のルールに従っている必要があります。パラメーターは関数に対してローカルです。同じパラメーター名を他の関数で使用できます。パラメーターは定数の代わりに使用できます。テーブル名、列名、または他のデータベースオブジェクトの名前の代わりに使用することはできません。

ANSI_WARNINGS は、ストアドプロシージャ、ユーザー定義関数でパラメーターを渡すとき、またはバッチステートメントで変数を宣言して設定する場合には受け入れられません。たとえば、変数を char(3) と定義し、これに 4 文字以上の値を設定すると、データが定義されたサイズに合わせて切り捨てられてから、INSERT または UPDATE ステートメントが成功します。

[ type_schema_name. ] parameter_data_type

パラメーターデータ型、および必要に応じて、それが属するスキーマ。 Transact-SQL 関数の場合は、CLR ユーザー定義型およびユーザー定義テーブル型を含めたデータ型のうち、timestamp を除くすべてのデータ型を指定できます。 CLR 関数の場合、CLR ユーザー定義型を含むすべてのデータ型は、 text、 ntext、 image、ユーザー定義テーブル型、および timestamp データ型を除きます。 transact-SQL 関数または CLR 関数では、 cursor および table の非スカラー型をパラメーターデータ型として指定することはできません。

type_schema_nameが指定されていない場合、データベースエンジンは次の順序でscalar_parameter_data_typeを検索します。

SQL Server システムデータ型の名前を含むスキーマ
現在のデータベースにおける現在のユーザーの既定のスキーマ。
現在のデータベースの dbo スキーマ。

[ = default ]

パラメーターの既定値。 default 値が定義されている場合は、パラメーターに値を指定せずに関数を実行できます。

varchar(max)とvarbinary(max)データ型を除き、CLR 関数に対して既定のパラメーター値を指定できます。

関数のパラメーターに既定値がある場合は、既定値を取得するために関数が呼び出されたときにキーワード DEFAULT を指定する必要があります。この動作は、ストアドプロシージャで既定値を持つパラメーターを使用する場合とは異なります。ストアドプロシージャの場合は、パラメーターを省略すると既定値が暗黙的に使用されます。ただし、EXECUTE ステートメントを使用してスカラー関数を呼び出すときは、DEFAULT キーワードは必要ありません。

READONLY

関数の定義内でパラメーターを更新または変更できないことを示します。 READONLY は、ユーザー定義のテーブル型パラメーター (TVP) に必要であり、他のパラメーター型には使用できません。

return_data_type

スカラーユーザー定義関数の戻り値。 Transact-SQL 関数の場合は、CLR ユーザー定義型を含めたデータ型のうち、timestamp データ型を除くすべてのデータ型を指定できます。 CLR 関数の場合は、CLR ユーザー定義型を含めたデータ型のうち、text、ntext、image、timestamp を除くすべてのデータ型を指定できます。 cursor および table の非スカラー型は、Transact-SQL 関数または CLR 関数では戻り値のデータ型として指定できません。

function_body

テーブルの変更などの副作用を生成しない一連の Transact-SQL ステートメントで、関数の値を定義することを指定します。 function_body は、スカラー関数と複数ステートメントのテーブル値関数 (MSTVF) でのみ使用されます。

スカラー関数の function_body は、総合してスカラー値と評価される一連の Transact-SQL ステートメントです。

MSTVF では、 function_body は、 TABLE 戻り変数を設定する一連の Transact-SQL ステートメントです。

scalar_expression

スカラー関数が返すスカラー値を指定します。

TABLE

テーブル値関数 (TVF) の戻り値がテーブルになるように指定します。 TVF に渡すことができるのは、定数と @local_variables のみです。

インライン TVF では、 TABLE 戻り値は 1 つの SELECT ステートメントを介して定義されます。インライン関数には、戻り値の変数が関連付けられません。

MSTVF では、 @return_variable は TABLE 変数であり、関数の値として返される必要がある行を格納および蓄積するために使用されます。 @return_variable は、Transact-SQL 関数にのみ指定でき、CLR 関数には指定できません。

select_stmt

インラインテーブル値関数 (TVF) の戻り値を定義する単一の SELECT ステートメント。

ORDER (<order_clause>)

テーブル値関数から結果が返される順序を指定します。詳細については、「 CLR テーブル値関数で並べ替え順序を使用するこの記事で後述するセクションを参照してください。

EXTERNAL NAME <method_specifier>assembly_name.class_name.method_name

適用対象: SQL Server 2008 (10.0.x) SP 1 以降のバージョン。

アセンブリおよび作成した関数名が参照するメソッドを指定します。

assembly_name - 次の name 列にある値と一致する必要があります: SELECT * FROM sys.assemblies;

CREATE ASSEMBLY ステートメントで使用された名前。
class_name - 次の assembly_name 列にある値と一致する必要があります: SELECT * FROM sys.assembly_modules;

多くの場合、値には、埋め込まれたピリオドまたはドット (.) が含まれています。このような場合、Transact-SQL 構文では、値が角かっこ ([]) のペアまたは二重引用符 ("") のペアで囲まれている必要があります。
method_name - 次の method_name 列にある値と一致する必要があります: SELECT * FROM sys.assembly_modules;

メソッドを静的にする必要があります。

すべての型が MyFood 名前空間にある MyFood.dll の一般的な例では、EXTERNAL NAME値をMyFood.[MyFood.MyClass].MyStaticMethodできます。

既定では、SQL Server で CLR コードを実行することはできません。共通言語ランタイムモジュールを参照するデータベースオブジェクトを作成、変更、および削除できます。ただし、 clr enabled オプションを有効にするまで、SQL Server でこれらの参照を実行することはできません。このオプションを有効にするには、sp_configure を使用します。このオプションは、包含データベースでは使用できません。

<table_type_definition> ( { <column_definition><column_constraint | <computed_column_definition> } [ <table_constraint> ] [ , ...n ] )

Transact-SQL 関数のテーブルデータ型を定義します。テーブルの定義には、列の定義、および列またはテーブルの制約が含まれます。テーブルは、常にプライマリファイルグループに保存されます。

<clr_table_type_definition> ( { column_namedata_type } [ , ...n ] )

適用対象: SQL Server 2008 (10.0.x) SP 1 以降のバージョン、および Azure SQL Database (一部のリージョンでは Preview)。

CLR 関数のテーブルデータ型を定義します。テーブルの定義には、列名およびデータ型のみが含まれます。テーブルは、常にプライマリファイルグループに保存されます。

NULL | NOT NULL

ネイティブにコンパイルされた、スカラーユーザー定義関数でのみサポートされます。詳しくは、「インメモリ OLTP でのユーザー定義のスカラー関数」をご覧ください。

NATIVE_COMPILATION

ユーザー定義の関数をネイティブでコンパイルするかどうかを示します。この引数は、ネイティブにコンパイルされたスカラーユーザー定義関数に必要です。

BEGIN ATOMIC WITH

ネイティブコンパイルスカラーユーザー定義関数に対してのみ必須であり、サポートされます。詳細については、「ネイティブプロシージャ Atomic ブロック」を参照してください。

SCHEMABINDING

SCHEMABINDING引数は、ネイティブコンパイルのスカラーユーザー定義関数に必要です。

EXECUTE AS

EXECUTE AS は、ネイティブコンパイルのスカラーユーザー定義関数に必要です。

<function_option> ::= と <clr_function_option> ::=

関数に次のオプションの 1 つ以上があることを指定します。

暗号化

適用対象: SQL Server 2008 (10.0.x) SP 1 以降のバージョン。

データベースエンジンが、CREATE FUNCTION ステートメントの元のテキストを難読化された形式に変換することを示します。難読化の出力は、どのカタログビューにも直接表示されません。システムテーブルまたはデータベースファイルにアクセスできないユーザーは、難読化されたテキストを取得できません。ただし、データベース管理者の Diagnostic 接続経由でシステムテーブルにアクセスしたりデータベースファイルに直接アクセスしたりできる特権ユーザーは、このテキストを使用できます。また、サーバープロセスにデバッガーをアタッチできるユーザーは、実行時、元のプロシージャをメモリから取得できます。システムメタデータのアクセス方法について詳しくは、「メタデータ表示の構成」をご覧ください。

このオプションを使用すると、その関数を SQL Server レプリケーションの一部としてパブリッシュできなくなります。 CLR 関数には、このオプションを指定できません。

SCHEMABINDING

参照するデータベースオブジェクトに対して、その関数がバインドされるように指定します。 SCHEMABINDINGを指定すると、基本オブジェクトを関数定義に影響する方法で変更することはできません。まず関数定義を変更または削除して、変更するオブジェクトとの依存関係を解消する必要があります。

関数が参照するオブジェクトへのバインドは、次のいずれかの操作が行われた場合にのみ削除されます。

関数を削除した場合。
関数を、SCHEMABINDING オプションを指定せずに ALTER ステートメントを使用して変更した場合。

関数をスキーマにバインドできるのは、次の条件が満たされている場合に限られます。

関数が Transact-SQL 関数です。
関数が参照するユーザー定義関数とビューも同様にスキーマにバインドされている。
関数が参照するオブジェクトが、2 つの要素から成る名前を使用して参照されている。
関数およびその関数が参照するオブジェクトが、同じデータベースに属している。
CREATE FUNCTION ステートメントを実行したユーザーが、その関数で参照されるデータベースオブジェクトに対する REFERENCES 権限を持っている。

RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT

スカラー関数の OnNULLCall 属性を指定します。指定しない場合、 CALLED ON NULL INPUT は既定で暗黙的に指定されます。つまり、 NULL が引数として渡された場合でも、関数本体が実行されます。

CLR 関数でRETURNS NULL ON NULL INPUTが指定されている場合、受け取る引数のいずれかがNULLされたときに、関数の本体を実際に呼び出さずに SQL Server がNULLを返すことができることを示します。 <method_specifier>で指定された CLR 関数のメソッドに既にRETURNS NULL ON NULL INPUTを示すカスタム属性があるが、CREATE FUNCTION ステートメントがCALLED ON NULL INPUTを示している場合、CREATE FUNCTION ステートメントが優先されます。 clr テーブル値関数には、 OnNULLCall 属性を指定できません。

EXECUTE AS

ユーザー定義関数が実行されるセキュリティコンテキストを指定します。これにより、SQL Server が、関数で参照されているデータベースオブジェクトに対する権限を検証する際に使用するユーザーアカウントを制御できます。

EXECUTE AS インラインテーブル値関数には指定できません。

詳細については、「EXECUTE AS 句 (Transact-SQL)」を参照してください。

INLINE = { ON | OFF }

適用対象: SQL Server 2019 (15.x) 以降のバージョン、および Azure SQL Database。

このスカラー UDF をインライン化する必要があるかどうかを指定します。この句は、スカラーユーザー定義関数にのみ適用されます。 INLINE 句は必須ではありません。 INLINE句が指定されていない場合、UDF がインライン化可能かどうかに基づいて、ONまたはOFFに自動的に設定されます。 INLINE = ONが指定されていても、UDF がインライン化できないことがわかった場合は、エラーがスローされます。詳細については、「スカラー UDF のインライン化」を参照してください。

<column_definition> ::=

Table データ型を定義します。テーブルの宣言には、列の定義および制約が含まれます。 CLR 関数では、column_name と data_type のみを指定できます。

column_name

テーブル内の列の名前。列名は、識別子のルールに従っていること、およびテーブル内で一意であることが必要です。 column_name は 1 ～ 128 文字で指定できます。

data_type

列のデータ型を指定します。 Transact-SQL 関数の場合は、CLR ユーザー定義型を含めたデータ型のうち、timestamp を除くすべてのデータ型を指定できます。 CLR 関数の場合、CLR ユーザー定義型を含むすべてのデータ型は、 text、 ntext、 image、 char、 varchar、 varchar(max)、および timestamp を除きます。非スカラー型 cursor は、Transact-SQL 関数または CLR 関数では列データ型として指定できません。

DEFAULT constant_expression

挿入の際に明示的な値を指定しない場合に、列に入力される値を指定します。 constant_expression は定数、 NULL、またはシステム関数の値です。 DEFAULT 定義は、 IDENTITY プロパティを持つ列を除く任意の列に適用できます。 DEFAULT CLR テーブル値関数には指定できません。

COLLATE collation_name

列の照合順序を指定します。指定しない場合、データベースの既定の照合順序が列に割り当てられます。照合順序名には、Windows 照合順序名または SQL 照合順序名を指定できます。照合順序の一覧と詳細については、「Windows 照合順序名 (Transact-SQL)」と「SQL Server 照合順序名 (Transact-SQL)」をご覧ください。

COLLATE句を使用すると、char、varchar、nchar、および nvarchar データ型の列の照合順序のみを変更できます。 COLLATE CLR テーブル値関数には指定できません。

ROWGUIDCOL

新しい列が行グローバル一意識別子列であることを示します。テーブルごとに 1 つの uniqueidentifier 列のみを ROWGUIDCOL 列として指定できます。 ROWGUIDCOL プロパティは、uniqueidentifier 列にのみ割り当てることができます。

ROWGUIDCOL プロパティは、列に格納されている値の一意性を強制しません。また、テーブルに挿入された新しい行の値も自動的には生成されません。列ごとに一意の値を生成するには、INSERT ステートメントで NEWID 関数を使用します。既定値を指定できます。ただし、 NEWID を既定値として指定することはできません。

IDENTITY

新しい列が ID 列であることを指定します。テーブルに行が新しく追加されると、SQL Server は列に一意な増加値を設定します。 ID 列は、通常、テーブルの一意の行識別子として機能するために、 PRIMARY KEY 制約と共に使用されます。 IDENTITY プロパティは、tinyint、smallint、int、bigint、decimal(p,0)、numeric(p,0) のいずれかの列に割り当てることができます。 ID 列は 1 つのテーブルにつき 1 つだけ作成できます。バインドされた既定値と DEFAULT 制約は、ID 列では使用できません。 seed と increment は、両方を指定するか、どちらも指定しないでください。どちらも指定しないときの既定値は (1,1) です。

IDENTITY CLR テーブル値関数には指定できません。

seed

テーブルの最初の行に割り当てられる整数値。

increment

テーブル内の連続する行の seed 値に追加する整数値。

<column_constraint> ::= と <table_constraint> ::=

指定された列またはテーブルの制約を定義します。 CLR 関数の場合、使用できる制約の型は NULLのみです。名前付き制約は許可されません。

NULL | NOT NULL

列で NULL 値を許すかどうかを示します。 NULL は厳密には制約ではありませんが、 NOT NULLと同じように指定できます。 NOT NULL CLR テーブル値関数には指定できません。

PRIMARY KEY

一意のインデックスを使用して、指定した列にエンティティの整合性を適用する制約。テーブル値ユーザー定義関数では、 PRIMARY KEY 制約はテーブルごとに 1 つの列にのみ作成できます。 PRIMARY KEY CLR テーブル値関数には指定できません。

UNIQUE

一意なインデックスによって、指定された 1 つ以上の列にエンティティの整合性を提供する制約。テーブルには複数の UNIQUE 制約を含めることができます。 UNIQUE CLR テーブル値関数には指定できません。

CLUSTERED | NONCLUSTERED

PRIMARY KEY制約またはUNIQUE制約に対してクラスター化インデックスまたは非クラスター化インデックスが作成されることを示します。 PRIMARY KEY 制約では CLUSTEREDが使用され、 UNIQUE 制約では NONCLUSTEREDが使用されます。

CLUSTERED は、1 つの制約にのみ指定できます。 UNIQUE制約にCLUSTEREDが指定されていて、PRIMARY KEY制約も指定されている場合、PRIMARY KEYはNONCLUSTEREDを使用します。

CLUSTERED CLR テーブル値関数には NONCLUSTERED を指定できません。

CHECK

1 つ以上の列に入力できる値を制限することによってドメインの整合性を設定する制約。 CHECK CLR テーブル値関数には制約を指定できません。

logical_expression

TRUEまたはFALSEを返す論理式。

<computed_column_definition> ::=

計算列を指定します。計算列の詳細については、「CREATE TABLE (Transact-SQL)」を参照してください。

column_name

計算列の名前。

computed_column_expression

計算列の値を定義する式。

<index_option> ::=

PRIMARY KEYまたはUNIQUEインデックスのインデックスオプションを指定します。インデックスオプションの詳細については、「CREATE INDEX (Transact-SQL)」を参照してください。

PAD_INDEX = { ON | OFF }

インデックスの埋め込みを指定します。既定値は、OFF です。

FILLFACTOR = fillfactor

インデックスの作成時または変更時に、データベースエンジンが各インデックスページのリーフレベルをどの程度まで埋めるかを、パーセント値で指定します。 fillfactor 値には、1 ～ 100 の整数値を指定してください。既定値は 0 です。

IGNORE_DUP_KEY = { ON | OFF }

挿入操作で、一意のインデックスに重複するキー値を挿入しようとした場合のエラー応答を指定します。 IGNORE_DUP_KEY オプションは、インデックスが作成または再構築された後の挿入操作のみに適用されます。既定値は、OFF です。

STATISTICS_NORECOMPUTE = { ON | OFF}

分布統計を再計算するかどうかを指定します。既定値は、OFF です。

ALLOW_ROW_LOCKS = { ON | OFF }

行ロックを許可するかどうかを指定します。既定値は、ON です。

ALLOW_PAGE_LOCKS = { ON | OFF }

ページロックを許可するかどうかを指定します。既定値は、ON です。

ベストプラクティス

ユーザー定義関数が SCHEMABINDING 句で作成されていない場合、基になるオブジェクトに加えられた変更が関数の定義に影響し、呼び出されたときに予期しない結果が生成される可能性があります。基になるオブジェクトに対する変更のために関数が古くならないように、次のいずれかの方法を実装することをお勧めします。

関数を作成するときに、 WITH SCHEMABINDING 句を指定します。このオプションを使用すると、関数も変更されない限り、関数定義で参照されているオブジェクトを変更できなくなります。
関数の定義で指定されているオブジェクトを変更した後に sp_refreshsqlmodule ストアドプロシージャを実行します。

インラインテーブル値関数 (インライン TVF) と複数ステートメントのテーブル値関数 (MSTVF) の詳細とパフォーマンスに関する考慮事項については、「ユーザー定義関数の作成 (データベースエンジン)を参照してください。

データ型

CLR 関数で指定するパラメーターは、以前 scalar_parameter_data_type 用に定義されていた SQL Server 型にしてください。 SQL Server システムデータ型と CLR 統合データ型または .NET Framework 共通言語ランタイムデータ型の比較の詳細については、「 CLR パラメーターデータのマッピング」を参照してください。

SQL Server がクラスでオーバーロードされたときに正しいメソッドを参照するには、 <method_specifier> で示されるメソッドに次の特性が必要です。

[ , ...n ]で指定された数のパラメーターを受け取ります。
すべてのパラメーターを、参照ではなく値で受け取る。
SQL Server 関数で指定された型と互換性のあるパラメーター型を使用します。

CLR 関数の戻り値のデータ型でテーブル型 (RETURNS TABLE) を指定する場合、 <method_specifier> のメソッドの戻り値のデータ型は IEnumerator 型または IEnumerable型である必要があり、インターフェイスは関数の作成者によって実装されていることを前提としています。 Transact-SQL 関数とは異なり、CLR 関数は<table_type_definition>にPRIMARY KEY、UNIQUE、またはCHECK制約を含めることはできません。 <table_type_definition> に指定する列のデータ型は、<method_specifier> のメソッドの実行時に返される結果セット内の、対応する列の型に一致する必要があります。この型チェックは、関数の作成時には実行されません。

CLR 関数のプログラミング方法について詳しくは、「CLR ユーザー定義関数」をご覧ください。

解説

スカラー関数は、計算列と CHECK 制約定義を含むスカラー式が使用される場所で呼び出すことができます。スカラー関数は、 EXECUTE (Transact-SQL) ステートメントを使用して実行することもできます。スカラー関数は、2 つ以上の要素から構成される名前を使用して呼び出す必要があります (<schema>.<function>)。マルチパート名の詳細については、「Transact-SQL 構文表記規則 (Transact-SQL)」を参照してください。 SELECT、INSERT、UPDATE、DELETE の各ステートメントの FROM 句でテーブル式を使用できる場合は、テーブル値関数を呼び出すことができます。詳細については、「ユーザー定義関数の実行を参照してください。

相互運用性

関数で有効なステートメントは以下のとおりです。

代入ステートメント。
TRY...CATCH ステートメントを除く、フロー制御ステートメント。
ローカルデータ変数およびローカルカーソルを定義する DECLARE ステートメント。
ローカル変数に値を代入する式を持つ選択リストが含まれている SELECT ステートメント。
関数内で宣言、オープン、クローズ、割り当ての解除を実行するローカルカーソルを参照するカーソル操作。 INTO句を使用してローカル変数に値を割り当てるFETCHステートメントのみが許可されます。クライアントにデータを返すFETCHステートメントは許可されません。
ローカルなテーブル変数を変更する、INSERT、UPDATE、および DELETE ステートメント。
拡張ストアドプロシージャを呼び出す EXECUTE ステートメント。

詳細については、「ユーザー定義関数の作成 (データベースエンジン)」を参照してください。

計算列の相互運用性

関数には次のプロパティがあります。これらのプロパティの値によって、保存される計算列またはインデックス付き計算列で関数を使用できるかどうかが決まります。

プロパティ	説明	メモ
`IsDeterministic`	関数が決定的か非決定的かを示します。	決定的関数では、ローカルデータアクセスが可能です。たとえば、特定の入力値のセットを使用して呼び出されるたびに常に同じ結果を返し、データベースの同じ状態を持つ関数には、決定論的なラベルが付けられます。
`IsPrecise`	関数が正確か不正確かを示します。	不正確な関数には、浮動小数点演算などの演算を含みます。
`IsSystemVerified`	関数の有効桁数のプロパティと決定性のプロパティは、SQL Server で確認できます。
`SystemDataAccess`	関数が、SQL Server のローカルインスタンスのシステムデータ (システムカタログまたは仮想システムテーブル) にアクセスします。
`UserDataAccess`	関数が、SQL Server のローカルインスタンスのユーザーデータにアクセスします。	ユーザー定義テーブルと一時テーブルが含まれますが、テーブル変数は含まれません。

Transact-SQL 関数の有効桁数のプロパティと決定性のプロパティは、SQL Server によって自動的に決定されます。 CLR 関数のデータアクセスプロパティと決定性のプロパティは、ユーザーが指定できます。詳細については、「 CLR 統合: CLR ルーチンのカスタム属性」を参照してください。

これらのプロパティの現在の値を表示するには、 OBJECTPROPERTYEX (Transact-SQL)を使用します。

重要

関数は、決定的になるように SCHEMABINDING を使用して作成する必要があります。

ユーザー定義関数に以下のプロパティ値がある場合、その関数を呼び出す計算列をインデックスで使用できます。

IsDeterministic は true です
IsSystemVerified が true (計算列が永続化されていない場合)
UserDataAccess は false です
SystemDataAccess は false です

詳細については、「計算列のインデックス」を参照してください。

関数から拡張ストアドプロシージャを呼び出す

拡張ストアドプロシージャは、関数内から呼び出すときに、結果セットをクライアントに返すことはできません。クライアントに結果セットを返す ODS API は、 FAILを返します。拡張ストアドプロシージャは、SQL Server のインスタンスに接続できます。ただし、拡張ストアドプロシージャを呼び出した関数と同じトランザクションを結合しないでください。

バッチまたはストアドプロシージャからの呼び出しと同様に、拡張ストアドプロシージャは、SQL Server が実行されている Windows セキュリティアカウントのコンテキストで実行されます。ストアドプロシージャの所有者は、ユーザーに EXECUTE アクセス許可を付与するときに、このシナリオを考慮する必要があります。

制限事項

ユーザー定義関数は、データベースの状態を変更するアクションの実行に使うことはできません。

出力先がテーブルである OUTPUT INTO 句をユーザー定義関数に含めることはできません。

次の Service Broker ステートメントは、Transact-SQL ユーザー定義関数の定義には含められません。

BEGIN DIALOG CONVERSATION
END CONVERSATION
GET CONVERSATION GROUP
MOVE CONVERSATION
RECEIVE
SEND

ユーザー定義関数は入れ子にすることができます。つまり、1 つのユーザー定義関数で、別のユーザー定義関数を呼び出すことができます。呼び出された関数が実行を開始すると入れ子レベルが 1 つ上がり、呼び出された関数が実行を終了するとレベルが 1 つ下がります。ユーザー定義関数は、32 レベルまで入れ子にすることができます。入れ子レベルが最大値を超えると、関数チェーン全体の呼び出しが失敗します。 Transact-SQL ユーザー定義関数からマネージドコードへの参照は、32 レベルの入れ子制限のうちの 1 レベルとカウントします。マネージドコード内から呼び出されたメソッドは、この制限としてはカウントされません。

CLR テーブル値関数で並べ替え順序を使用する

CLR テーブル値関数で ORDER 句を使用する場合は、以下のガイドラインに従ってください。

結果が常に指定した順序で並べられるようにする必要があります。結果が指定された順序でない場合、クエリの実行時に SQL Server によってエラーメッセージが生成されます。
ORDER 句を指定した場合は、テーブル値関数の出力を列の照合順序に従って (明示的または暗黙的に) 並べ替える必要があります。たとえば、列の照合順序が中国語の場合、返される結果は中国語の並べ替え規則に従って並べ替える必要があります。 (照合順序は、テーブル値関数の DDL で指定するか、データベースの照合順序から取得します)。
SQL Server では、クエリプロセッサが ORDER 句を使用してさらに最適化を実行するかどうかに関係なく、結果を返しながら、常に句が検証されます。 ORDER句は、クエリプロセッサに役立つことがわかっている場合にのみ使用してください。
SQL Server のクエリプロセッサでは、次の場合に ORDER 句が自動的に利用されます。
- ORDER 句がインデックスと互換性のある挿入クエリ。
- ORDER 句と互換性のある ORDER BY 句。
- GROUP BY が ORDER 句と互換性のある、集計。
- 個別の列が ORDER 句と互換性のある DISTINCT 集計。

ORDER句は、クエリでORDER BYが指定されていない限り、SELECT クエリの実行時に順序付けされた結果を保証しません。テーブル値関数の並べ替え順に含まれる列に対してクエリを実行する方法については、「sys.function_order_columns (Transact-SQL)」を参照してください。

Metadata

次の表に、ユーザー定義関数に関するメタデータを返すために使用できるシステムカタログビューを示します。

システムビュー	説明
sys.sql_modules	Examples セクションの E の例を参照してください。
sys.assembly_modules	CLR ユーザー定義関数の情報を表示します。
sys.parameters	ユーザー定義関数で定義されているパラメーターの情報を表示します。
sys.sql_expression_dependencies	関数が参照する基になるオブジェクトを表示します。

アクセス許可

データベースの CREATE FUNCTION 権限と、関数を作成するスキーマの ALTER 権限が必要です。関数でユーザー定義型が指定されている場合は、その型に対する EXECUTE 権限が必要です。

例

UDF に関するその他の例とパフォーマンスに関する考慮事項については、「ユーザー定義関数の作成 (データベースエンジン)を参照してください。

A. ISO 週を計算するスカラー値ユーザー定義関数を使用する

次の例では、ユーザー定義関数 ISOweek を作成します。この関数は、日付引数を受け取って、ISO 週番号を計算します。この関数が正しい計算を行うためには、関数を呼び出す前に SET DATEFIRST 1 を呼び出す必要があります。

この例では、 EXECUTE AS 句 (Transact-SQL) 句を使用して、ストアドプロシージャを実行できるセキュリティコンテキストを指定する方法も示します。この例では、 CALLER オプションは、プロシージャを呼び出すユーザーのコンテキストでプロシージャを実行することを指定します。指定できるその他のオプションは、SELF、OWNER、および user_name です。

関数呼び出しを次に示します。 DATEFIRST が 1 に設定されます。

CREATE FUNCTION dbo.ISOweek (@DATE DATETIME)
RETURNS INT
WITH EXECUTE AS CALLER
AS
BEGIN
    DECLARE @ISOweek INT;

    SET @ISOweek = DATEPART(wk, @DATE) + 1 -
        DATEPART(wk, CAST(DATEPART(yy, @DATE) AS CHAR(4)) + '0104');

    --Special cases: Jan 1-3 may belong to the previous year
    IF (@ISOweek = 0)
        SET @ISOweek = dbo.ISOweek(CAST(DATEPART(yy, @DATE) - 1 AS CHAR(4))
           + '12' + CAST(24 + DATEPART(DAY, @DATE) AS CHAR(2))) + 1;

    --Special case: Dec 29-31 may belong to the next year
    IF ((DATEPART(mm, @DATE) = 12)
        AND ((DATEPART(dd, @DATE) - DATEPART(dw, @DATE)) >= 28))
    SET @ISOweek = 1;

    RETURN (@ISOweek);
END;
GO

SET DATEFIRST 1;

SELECT dbo.ISOweek(CONVERT(DATETIME, '12/26/2004', 101)) AS 'ISO Week';

結果セットは次のとおりです。

ISO Week
----------------
52

B. インラインテーブル値関数を作成する

次の例では、AdventureWorks2022 データベースのインラインテーブル値関数が返されます。ここでは、店舗に販売された製品ごとに 3 つの列を返します。ProductID、Name、YTD Total (今年に入ってからの店舗別合計の集計) です。

CREATE FUNCTION Sales.ufn_SalesByStore (@storeid INT)
RETURNS TABLE
AS
RETURN (
    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'
    FROM Production.Product AS P
    INNER JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
    INNER JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
    INNER JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID
    WHERE C.StoreID = @storeid
    GROUP BY P.ProductID, P.Name
);
GO

関数を呼び出すには、次のクエリを実行します。

SELECT * FROM Sales.ufn_SalesByStore (602);

C: 複数ステートメントのテーブル値関数を作成する

次の例では、AdventureWorks2022 データベースにインラインテーブル値関数 fn_FindReports(InEmpID) を作成します。有効な従業員 ID をこの関数に指定すると、その従業員に対して直接または間接の報告関係にあるすべての従業員に対応した表が返されます。この関数は、再帰共通テーブル式 (CTE) を使用して、従業員の階層リストを生成します。再帰 CTE の詳細については、「WITH common_table_expression (Transact-SQL)」を参照してください。

CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INT)
RETURNS @retFindReports TABLE (
    EmployeeID INT PRIMARY KEY NOT NULL,
    FirstName NVARCHAR(255) NOT NULL,
    LastName NVARCHAR(255) NOT NULL,
    JobTitle NVARCHAR(50) NOT NULL,
    RecursionLevel INT NOT NULL
    )
    --Returns a result set that lists all the employees who report to the
    --specific employee directly or indirectly.
AS
BEGIN
    WITH EMP_cte (
        EmployeeID,
        OrganizationNode,
        FirstName,
        LastName,
        JobTitle,
        RecursionLevel
        ) -- CTE name and columns
    AS (
        -- Get the initial list of Employees for Manager n
        SELECT e.BusinessEntityID,
            OrganizationNode = ISNULL(e.OrganizationNode, CAST('/' AS HIERARCHYID)),
            p.FirstName,
            p.LastName,
            e.JobTitle,
            0
        FROM HumanResources.Employee e
        INNER JOIN Person.Person p
            ON p.BusinessEntityID = e.BusinessEntityID
        WHERE e.BusinessEntityID = @InEmpID
        
        UNION ALL
        
        -- Join recursive member to anchor
        SELECT e.BusinessEntityID,
            e.OrganizationNode,
            p.FirstName,
            p.LastName,
            e.JobTitle,
            RecursionLevel + 1
        FROM HumanResources.Employee e
        INNER JOIN EMP_cte
            ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode
        INNER JOIN Person.Person p
            ON p.BusinessEntityID = e.BusinessEntityID
        )
    -- Copy the required columns to the result of the function
    INSERT @retFindReports
    SELECT EmployeeID,
        FirstName,
        LastName,
        JobTitle,
        RecursionLevel
    FROM EMP_cte

    RETURN
END;
GO

-- Example invocation
SELECT EmployeeID,
    FirstName,
    LastName,
    JobTitle,
    RecursionLevel
FROM dbo.ufn_FindReports(1);
GO

D. CLR 関数を作成する

この例では、CLR 関数 len_s を作成します。関数が作成される前に、アセンブリ SurrogateStringFunction.dll がローカルデータベースに登録されます。

適用対象: SQL Server 2008 (10.0.x) SP 1 以降のバージョン。

DECLARE @SamplesPath NVARCHAR(1024);

-- You may have to modify the value of this variable if you have
-- installed the sample in a location other than the default location.
SELECT @SamplesPath = REPLACE(physical_name,
    'Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\master.mdf',
    'Microsoft SQL Server\130\Samples\Engine\Programmability\CLR\'
)
FROM master.sys.database_files
WHERE name = 'master';

CREATE ASSEMBLY [SurrogateStringFunction]
FROM @SamplesPath + 'StringManipulate\CS\StringManipulate\bin\debug\SurrogateStringFunction.dll'
    WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

CREATE FUNCTION [dbo].[len_s] (@str NVARCHAR(4000))
RETURNS BIGINT
AS
EXTERNAL NAME [SurrogateStringFunction].[Microsoft.Samples.SqlServer.SurrogateStringFunction].[LenS];
GO

CLR テーブル値関数の作成方法の例については、「CLR テーブル値関数」を参照してください。

E. ユーザー定義関数の定義を表示する

SELECT DEFINITION,
    type
FROM sys.sql_modules AS m
INNER JOIN sys.objects AS o
    ON m.object_id = o.object_id
    AND type IN ('FN', 'IF', 'TF');
GO

ENCRYPTION オプションを使用して作成された関数の定義は、sys.sql_modulesでは表示できません。ただし、暗号化された関数に関するその他の情報が表示されます。

次の方法で共有

CREATE FUNCTION (Transact-SQL)

構文

引数

OR ALTER

schema_name

function_name

@parameter_name

[ type_schema_name. ] parameter_data_type

[ = default ]

READONLY

return_data_type

function_body

scalar_expression

TABLE

select_stmt

ORDER (<order_clause>)

EXTERNAL NAME <method_specifier>assembly_name.class_name.method_name

<table_type_definition> ( { <column_definition><column_constraint | <computed_column_definition> } [ <table_constraint> ] [ , ...n ] )

<clr_table_type_definition> ( { column_namedata_type } [ , ...n ] )

NULL | NOT NULL

NATIVE_COMPILATION

BEGIN ATOMIC WITH

SCHEMABINDING

EXECUTE AS

<function_option> ::= と <clr_function_option> ::=

暗号化

SCHEMABINDING

RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT

EXECUTE AS

INLINE = { ON | OFF }

<column_definition> ::=

column_name

data_type

DEFAULT constant_expression

COLLATE collation_name

ROWGUIDCOL

IDENTITY

seed

increment

<column_constraint> ::= と <table_constraint> ::=

NULL | NOT NULL

PRIMARY KEY

UNIQUE

CLUSTERED | NONCLUSTERED

CHECK

logical_expression

<computed_column_definition> ::=

column_name

computed_column_expression

<index_option> ::=

PAD_INDEX = { ON | OFF }

FILLFACTOR = fillfactor

IGNORE_DUP_KEY = { ON | OFF }

STATISTICS_NORECOMPUTE = { ON | OFF}

ALLOW_ROW_LOCKS = { ON | OFF }

ALLOW_PAGE_LOCKS = { ON | OFF }

ベスト プラクティス

データ型

解説

相互運用性

計算列の相互運用性

関数から拡張ストアド プロシージャを呼び出す

制限事項

CLR テーブル値関数で並べ替え順序を使用する

Metadata

アクセス許可

例

A. ISO 週を計算するスカラー値ユーザー定義関数を使用する

B. インライン テーブル値関数を作成する

C: 複数ステートメントのテーブル値関数を作成する

D. CLR 関数を作成する

E. ユーザー定義関数の定義を表示する

関連するコンテンツ

フィードバック

その他のリソース

ベストプラクティス

関数から拡張ストアドプロシージャを呼び出す

B. インラインテーブル値関数を作成する