CREATE FUNCTION (Transact-SQL)

[アーティクル]
11/19/2013

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

SELECT などの Transact-SQL ステートメント内で使用する
関数を呼び出すアプリケーション内で使用する
別のユーザー定義関数の定義内で使用する
ビューをパラメーター化したり、インデックス付きビューの機能を向上させる
テーブルの列を定義する
列の CHECK 制約を定義する
ストアドプロシージャを置換する

トピックリンクアイコン Transact-SQL 構文表記規則

構文

--Transact-SQL Scalar Function Syntax
CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type 
    [ = default ] [ READONLY ] } 
    [ ,...n ]
  ]
)
RETURNS return_data_type
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN 
        function_body 
        RETURN scalar_expression
    END
[ ; ]

--Transact-SQL Inline Table-Valued Function Syntax 
CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type 
    [ = default ] [ READONLY ] } 
    [ ,...n ]
  ]
)
RETURNS TABLE
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

--Transact-SQL Multistatement Table-valued Function Syntax
CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type 
    [ = default ] [READONLY] } 
    [ ,...n ]
  ]
)
RETURNS @return_variable TABLE <table_type_definition>
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN 
        function_body 
        RETURN
    END
[ ; ]

--Transact-SQL Function Clauses 
<function_option>::= 
{
    [ ENCRYPTION ]
  | [ SCHEMABINDING ]
  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
  | [ EXECUTE_AS_Clause ]
}

<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 Scalar Function Syntax
CREATE FUNCTION [ schema_name. ] function_name 
( { @parameter_name [AS] [ type_schema_name. ] parameter_data_type 
    [ = default ] } 
    [ ,...n ]
)
RETURNS { return_data_type }
    [ WITH <clr_function_option> [ ,...n ] ]
    [ AS ] EXTERNAL NAME <method_specifier>
[ ; ]

--CLR Table-Valued Function Syntax
CREATE FUNCTION [ schema_name. ] function_name 
( { @parameter_name [AS] [ type_schema_name. ] parameter_data_type 
    [ = default ] } 
    [ ,...n ]
)
RETURNS TABLE <clr_table_type_definition> 
    [ WITH <clr_function_option> [ ,...n ] ]
    [ ORDER ( <order_clause> ) ]
    [ AS ] EXTERNAL NAME <method_specifier>
[ ; ]

--CLR Function Clauses
<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 ] )

引数

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

注

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

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

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

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

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

注
ストアドプロシージャまたはユーザー定義関数でパラメーターを渡すとき、あるいはバッチステートメントで変数を宣言して設定するときには、ANSI_WARNINGS が無視されます。たとえば、変数を char(3) として定義し、3 文字より長い値を指定すると、データは定義されたサイズに切り捨てられ、INSERT または UPDATE ステートメントは成功します。

ストアドプロシージャまたはユーザー定義関数でパラメーターを渡すとき、あるいはバッチステートメントで変数を宣言して設定するときには、ANSI_WARNINGS が無視されます。たとえば、変数を char(3) として定義し、3 文字より長い値を指定すると、データは定義されたサイズに切り捨てられ、INSERT または UPDATE ステートメントは成功します。

[ type_schema_name.] parameter_data_type
パラメーターのデータ型です。このデータ型が属するスキーマを指定することもできます。 Transact-SQL 関数の場合は、CLR ユーザー定義型およびユーザー定義テーブル型を含めたデータ型のうち、timestamp を除くすべてのデータ型を指定できます。 CLR 関数の場合は、CLR ユーザー定義型を含めたデータ型のうち、text、ntext、image、ユーザー定義テーブル型、および timestamp を除くすべてのデータ型を指定できます。非スカラー型の cursor および table は、Transact-SQL 関数と CLR 関数の両方でパラメーターのデータ型として指定できません。

type_schema_name を指定しない場合、データベースエンジンは次の順序で scalar_parameter_data_type を検索します。
- SQL Server システムデータ型の名前を含むスキーマ
- 現在のデータベースにおける現在のユーザーの既定のスキーマ
- 現在のデータベースの dbo スキーマ
[ =default ]
パラメーターの既定値です。 default 値が定義されている場合は、パラメーターに値を指定せずに関数を実行できます。

注

varchar(max) データ型および varbinary(max) データ型の場合を除いて、CLR 関数には既定のパラメーター値を指定できます。

関数のパラメーターに既定値がある場合に、既定値を取得する目的でその関数を呼び出すときは、DEFAULT キーワードを指定する必要があります。この動作は、ストアドプロシージャで既定値を持つパラメーターを使用する場合とは異なります。ストアドプロシージャの場合は、パラメーターを省略すると既定値が暗黙的に使用されます。ただし、EXECUTE ステートメントを使用してスカラー関数を呼び出す場合は、DEFAULT キーワードは必要ありません。
READONLY
パラメーターを関数の定義内で更新または変更できないことを示します。パラメーターの型がユーザー定義テーブル型の場合は、READONLY を指定する必要があります。
return_data_type
スカラーユーザー定義関数の戻り値です。 Transact-SQL 関数の場合は、CLR ユーザー定義型を含めたデータ型のうち、timestamp を除くすべてのデータ型を指定できます。 CLR 関数の場合は、CLR ユーザー定義型を含めたデータ型のうち、text、ntext、image、および timestamp を除くすべてのデータ型を指定できます。非スカラー型の cursor および table は、Transact-SQL 関数と CLR 関数の両方で戻り値のデータ型として指定できません。
function_body
総合して副作用 (テーブルの変更など) がない一連の Transact-SQL ステートメントが、関数の値を定義することを指定します。 function_body は、スカラー関数と複数ステートメントのテーブル値関数でのみ使用されます。

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

複数ステートメントのテーブル値関数の function_body は、TABLE 戻り変数にデータを格納する一連の Transact-SQL ステートメントです。
scalar_expression
スカラー関数が返すスカラー値を指定します。

注
varchar(max) データ型および varbinary(max) データ型の場合を除いて、CLR 関数には既定のパラメーター値を指定できます。

TABLE
テーブル値関数の戻り値がテーブルになるように指定します。テーブル値関数に渡すことができるのは、定数および @local\_variables だけです。

インラインテーブル値関数の TABLE 戻り値は、単一の SELECT ステートメントを使用して定義します。インライン関数には、関連付けられている戻り変数はありません。

複数ステートメントのテーブル値関数の @return\_variable は TABLE 変数で、その関数の値として返される行の格納および蓄積に使用されます。 @return\_variable は Transact-SQL 関数にのみ指定でき、CLR 関数には指定できません。

注意
FROM 句の複数ステートメントのテーブル値関数に結合することはできますが、パフォーマンスが低下する場合があります。 SQL Server では、複数ステートメント関数に含まれることがある一部のステートメントに対して、すべての最適化技法を使用できるわけではありません。そのため、最適化されていないクエリプランになる場合があります。パフォーマンスをできるだけ高くするには、可能な限り、関数間ではなくベーステーブル間の結合を使用してください。

FROM 句の複数ステートメントのテーブル値関数に結合することはできますが、パフォーマンスが低下する場合があります。 SQL Server では、複数ステートメント関数に含まれることがある一部のステートメントに対して、すべての最適化技法を使用できるわけではありません。そのため、最適化されていないクエリプランになる場合があります。パフォーマンスをできるだけ高くするには、可能な限り、関数間ではなくベーステーブル間の結合を使用してください。

select_stmt
インラインテーブル値関数の戻り値を定義する単一の SELECT ステートメントです。
ORDER (<order_clause>)
テーブル値関数から結果が返される順序を指定します。詳細については、このトピックで後述する「並べ替え順序の使用に関するガイダンス」を参照してください。

EXTERNAL NAME <method_specifier> assembly_name.class_name.method_name
関数にバインドするアセンブリのメソッドを指定します。 assembly_name は、表示がオンになっている現在のデータベースに存在する SQL Server のアセンブリと一致している必要があります。 class_name は、有効な SQL Server 識別子であること、およびアセンブリにクラスとして存在していることが必要です。クラス名に名前空間とその区切り文字のピリオド (.) が含まれる場合は、クラス名をかっこ ([ ]) または引用符 (" ") で区切る必要があります。 method_name は、有効な SQL Server 識別子であること、および指定されているクラスの静的メソッドとして存在していることが必要です。

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

既定では、SQL Server は CLR コードを実行できません。共通言語ランタイムモジュールを参照するデータベースオブジェクトを作成、変更、および削除することはできますが、SQL Server でこれらの参照を実行するには、clr enabled オプションを有効にする必要があります。このオプションを有効にするには、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] )
CLR 関数のテーブルデータ型を定義します。テーブルの定義には、列名およびデータ型のみが含まれます。テーブルは、常にプライマリファイルグループに保存されます。

<function_option>::= and <clr_function_option>::=

関数に以下のオプションを 1 つ以上指定します。

ENCRYPTION
データベースエンジンで、CREATE FUNCTION ステートメントの元のテキストを、暗号化した形式に変換することを示します。暗号化した形式の出力は、どのカタログビューでも直接見ることはできません。システムテーブルまたはデータベースファイルへのアクセス権を持たないユーザーは、暗号化した形式のテキストを取得できません。 DAC ポート経由でシステムテーブルにアクセスする権限、または直接データベースファイルにアクセスする権限を持っているユーザーは、このテキストを使用できます。また、サーバープロセスにデバッガーをアタッチできるユーザーは、実行時、元のプロシージャをメモリから取得できます。システムメタデータへのアクセスの詳細については、「メタデータ表示の構成」を参照してください。

このオプションを使用すると、その関数を 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 が指定されていると、SQL Server は、受け取った引数のいずれかが NULL であった場合に関数の本体を呼び出すことなく 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)」を参照してください。

注
インラインユーザー定義関数には EXECUTE AS を指定できません。

< 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 プロパティを持つ列を除くすべての列に適用できます。 CLR テーブル値関数には DEFAULT を指定できません。
COLLATE collation_name
列の照合順序を指定します。照合順序を指定しない場合、データベースの既定の照合順序が列に割り当てられます。照合順序名には、Windows 照合順序名または SQL 照合順序名を指定できます。照合順序の一覧と詳細については、「Windows 照合順序名 (Transact-SQL)」および「SQL Server 照合順序名 (Transact-SQL)」を参照してください。

COLLATE 句を使用して照合順序を変更できるのは、char 型、varchar 型、nchar 型、および nvarchar 型の列だけです。

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

ROWGUIDCOL プロパティは、列に格納されている値の一意性を設定しません。また、ROWGUIDCOL プロパティは、テーブルに挿入される新しい行の値を自動的に生成しません。各列に対して一意な値を生成するには、INSERT ステートメントで NEWID 関数を使用します。既定値も指定できますが、NEWID を既定値として指定することはできません。
IDENTITY
新しい列が ID 列であることを示します。テーブルに行が新しく追加されると、SQL Server は列に一意な増加値を設定します。 ID 列は通常、PRIMARY KEY 制約と共に使用され、テーブルの一意な行識別子 (ROWID) の役割を果たします。 IDENTITY プロパティは、tinyint 型、smallint 型、int 型、bigint 型、decimal(p,0) 型、または numeric(p,0) 型の列に割り当てることができます。 ID 列は、1 つのテーブルにつき、1 つだけ作成できます。バインドされた既定値および DEFAULT 制約を ID 列と組み合わせて使用することはできません。 seed と increment は、両方を指定するか、どちらも指定しないでください。どちらも指定しないときの既定値は (1,1) です。

CLR テーブル値関数には IDENTITY を指定できません。
- seed
  テーブル内の先頭行に割り当てる整数値です。
- increment
  テーブル内の連続する行に対して、seed の値に加える整数値です。

< column_constraint >::= and < table_constraint>::=

指定された列またはテーブルの制約を定義します。 CLR 関数の場合、制約の種類として指定できるのは NULL だけです。名前付き制約は使用できません。

NULL | NOT NULL
列で NULL 値を許すかどうかを示します。 NULL は厳密には制約ではありませんが、NOT NULL と同じように指定することができます。 CLR テーブル値関数には NOT NULL を指定できません。
PRIMARY KEY
一意なインデックスによって、指定した列にエンティティの整合性を設定する制約です。テーブル値ユーザー定義関数では、1 つのテーブルにつき 1 つの列にのみ PRIMARY KEY 制約を作成できます。 CLR テーブル値関数には PRIMARY KEY を指定できません。
UNIQUE
一意なインデックスによって、指定した 1 つ以上の列にエンティティの整合性を持たせる制約です。 1 つのテーブルには複数の UNIQUE 制約を指定できます。 CLR テーブル値関数には UNIQUE を指定できません。
CLUSTERED | NONCLUSTERED
PRIMARY KEY 制約または UNIQUE 制約に対して、クラスター化インデックスまたは非クラスター化インデックスを作成することを示します。 PRIMARY KEY 制約では CLUSTERED が、UNIQUE 制約では NONCLUSTERED が、それぞれ使用されます。

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

CLR テーブル値関数には、CLUSTERED および NONCLUSTERED を指定できません。
CHECK
1 つ以上の列に入力できる値を制限することによってドメインの整合性を設定する制約です。 CLR テーブル値関数には CHECK 制約を指定できません。
- 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 ストアドプロシージャを実行します。

データ型

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

相互運用性

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

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

計算列の相互運用性

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

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

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

関数は、決定的になるようにスキーマバインドを使用して作成する必要があります。

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

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

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

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

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

メタデータ

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

システムビュー	説明
sys.sql_modules	Transact-SQL ユーザー定義関数の定義を表示します。次に例を示します。 `USE AdventureWorks2012; GO SELECT definition, type FROM sys.sql_modules AS m JOIN sys.objects AS o ON m.object_id = o.object_id AND type IN ('FN', 'IF', 'TF'); GO` ENCRYPTION オプションを使用して作成した関数の定義は、sys.sql_modules で表示することができません。ただし、暗号化された関数に関するその他の情報は表示されます。
sys.assembly_modules	CLR ユーザー定義関数の情報を表示します。
sys.parameters	ユーザー定義関数で定義されているパラメーターの情報を表示します。
sys.sql_expression_dependencies	関数が参照する基になるオブジェクトを表示します。

権限

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

使用例

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

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

また、この例では、EXECUTE AS 句を使用して、ストアドプロシージャを実行できるセキュリティコンテキストを指定します。この例のオプション CALLER は、プロシージャが呼び出し元ユーザーのコンテキストで実行されることを指定しています。指定可能な他のオプションは、SELF、OWNER、および user_name です。

以下に関数呼び出しを示します。 DATEFIRST が 1 に設定されていることに注意してください。

USE AdventureWorks2012;
GO
IF OBJECT_ID (N'dbo.ISOweek', N'FN') IS NOT NULL
    DROP FUNCTION dbo.ISOweek;
GO
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

----------------

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

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

USE AdventureWorks2012;
GO
IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL
    DROP FUNCTION Sales.ufn_SalesByStore;
GO
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 
    JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
    JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
    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. 複数ステートメントのテーブル値関数を作成する

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

USE AdventureWorks2012;
GO
IF OBJECT_ID (N'dbo.ufn_FindReports', N'TF') IS NOT NULL
    DROP FUNCTION dbo.ufn_FindReports;
GO
CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER)
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 (
        SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, 0 -- Get the initial list of Employees for Manager n
        FROM HumanResources.Employee e 
            INNER JOIN Person.Person p 
            ON p.BusinessEntityID = e.BusinessEntityID
        WHERE e.BusinessEntityID = @InEmpID
        UNION ALL
        SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, RecursionLevel + 1 -- Join recursive member to anchor
        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 がローカルデータベースに登録されます。

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\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf', 'Microsoft SQL Server\100\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 テーブル値関数」を参照してください。

次の方法で共有

CREATE FUNCTION (Transact-SQL)

構文

引数

ベストプラクティス

データ型

全般的な解説

相互運用性

計算列の相互運用性

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

制限事項と制約事項

並べ替え順序の使用に関するガイダンス

メタデータ

権限

使用例

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

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

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

D. CLR 関数を作成する

関連項目

参照

その他の技術情報

その他のリソース

次の方法で共有

CREATE FUNCTION (Transact-SQL)

構文

引数

ベスト プラクティス

データ型

全般的な解説

相互運用性

計算列の相互運用性

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

制限事項と制約事項

並べ替え順序の使用に関するガイダンス

メタデータ

権限

使用例

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

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

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

D. CLR 関数を作成する

関連項目

参照

その他の技術情報

その他のリソース

ベストプラクティス

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

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