CREATE FUNCTION (Transact-SQL)
ユーザー定義関数を作成します。これは、値を返す、保存された Transact-SQL または共通言語ランタイム (CLR) のルーチンです。ユーザー定義関数は、スカラー値関数とテーブル値関数のいずれかです。RETURNS 句でスカラー データ型のいずれかを指定した場合、関数はスカラー値関数になります。スカラー値関数は、複数の Transact-SQL ステートメントを使用して定義できます。 RETURNS 句で TABLE を指定した場合、関数はテーブル値関数になります。テーブル値関数は、関数の本文の定義方法に応じて、インライン関数と複数ステートメントの関数のいずれかに分類できます。詳細については、「ユーザー定義テーブル値関数」を参照してください。
.gif "トピック リンク アイコン")
構文
Scalar Functions
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
[ ; ]
Inline Table-Valued Functions
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 [ ) ]
[ ; ]
Multistatement Table-valued Functions
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
[ ; ]
CLR Scalar Functions
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 Functions
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>
[ ; ]
<order_clause> ::=
{
<column_name_in_clr_table_type_definition>
[ ASC | DESC ]
} [ ,...n]
Method Specifier<method_specifier>::= assembly_name.class_name.method_nameFunction Options<function_option>::=
{
[ ENCRYPTION ]
| [ SCHEMABINDING ]
| [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
| [ EXECUTE_AS_Clause ]
}
<clr_function_option>::=
}
[ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
| [ EXECUTE_AS_Clause ]
}
Table Type Definitions<table_type_definition>:: = ( { <column_definition> <column_constraint>
| <computed_column_definition> }
[ <table_constraint> ] [ ,...n ]
) <clr_table_type_definition>::= ( { column_name data_type } [ ,...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 }
}
引数
schema_name
ユーザー定義関数が属するスキーマの名前です。function_name
ユーザー定義関数の名前です。関数名は、識別子のルールに従っている必要があります。また、データベース内、およびそのスキーマに対して一意である必要があります。.gif "注意")
注意パラメーターを指定しない場合でも、関数名の後にはかっこが必要です。
@parameter\_name
ユーザー定義関数内のパラメーターです。1 つ以上のパラメーターを宣言できます。1 つの関数では、最高 2,100 個のパラメーターを使用できます。宜言した各パラメーターの値は、関数の実行時に、ユーザーが指定する必要があります (そのパラメーターの既定値が定義されていない場合)。
パラメーター名は、最初の文字をアット マーク (@) にして指定します。パラメーター名は識別子のルールに従っている必要があります。パラメーターは関数に対してローカルです。同じパラメーター名を他の関数で使用できます。パラメーターは定数の代わりとしてのみ使用できます。パラメーターは、テーブル名、列名、またはその他のデータベース オブジェクト名の代わりに使用することはできません。
注意ストアド プロシージャまたはユーザー定義関数でパラメーターを渡すとき、あるいはバッチ ステートメントで変数を宣言して設定するときには、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 キーワードを指定する必要があります。この動作は、ストアド プロシージャで既定値を持つパラメーターを使用する場合とは異なります。ストアド プロシージャの場合は、パラメーターを省略すると既定値が暗黙的に使用されます。
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
スカラー関数が返すスカラー値を指定します。TABLE
テーブル値関数の戻り値がテーブルになるように指定します。テーブル値関数に渡すことができるのは、定数および @local\_variables だけです。インライン テーブル値関数の TABLE 戻り値は、単一の SELECT ステートメントを使用して定義します。インライン関数には、関連付けられている戻り変数はありません。
複数ステートメントのテーブル値関数の @return\_variable は TABLE 変数で、その関数の値として返される行の格納および蓄積に使用されます。@return_variable は Transact-SQL 関数にのみ指定でき、CLR 関数には指定できません。
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 を使用します。
<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)」を参照してください。
< 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 制約と共に使用され、テーブルの一意な行識別子の役割を果たします。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 を返す論理式です。
- logical_expression
<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)」を参照してください。
メタデータ
次の表に、ユーザー定義関数に関するメタデータを返すときに使用できるシステム カタログ ビューを示します。
システム ビュー |
説明 |
|---|---|
Transact-SQL ユーザー定義関数の定義を表示します。例 : ENCRYPTION オプションを使用して作成した関数の定義は、sys.sql_modules で表示することができません。ただし、暗号化された関数に関するその他の情報は表示されます。 |
|
CLR ユーザー定義関数の情報を表示します。 |
|
ユーザー定義関数で定義されているパラメーターの情報を表示します。 |
|
関数が参照する基になるオブジェクトを表示します。 |
権限
データベースの CREATE FUNCTION 権限と、関数を作成するスキーマの ALTER 権限が必要です。関数でユーザー定義型が指定されている場合は、その型に対する EXECUTE 権限が必要です。
例
A. ISO 週番号を計算するスカラー値ユーザー定義関数を使用する
次の例では、ユーザー定義関数 ISOweek を作成します。この関数は、日付引数を受け取って、ISO 週番号を計算します。この関数が正しい計算を行うためには、関数を呼び出す前に SET DATEFIRST 1 を呼び出す必要があります。
また、この例では、EXECUTE AS 句を使用して、ストアド プロシージャを実行できるセキュリティ コンテキストを指定します。この例のオプション CALLER は、プロシージャが呼び出し元ユーザーのコンテキストで実行されることを指定しています。指定可能な他のオプションは、SELF、OWNER、および user_name です。
以下に関数呼び出しを示します。DATEFIRST が 1 に設定されていることに注意してください。
USE AdventureWorks;
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
----------------
52
B. インライン テーブル値関数を作成する
次の例では、インライン テーブル値関数を返します。ここでは、店舗に販売された製品ごとに 3 つの列を返します。ProductID、Name、および YTD Total (今年に入ってからの店舗別合計の集計) です。
USE AdventureWorks;
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 'YTD 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
WHERE SH.CustomerID = @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 AdventureWorks;
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,
Name nvarchar(255) NOT NULL,
Title nvarchar(50) NOT NULL,
EmployeeLevel int NOT NULL,
Sort nvarchar (255) NOT NULL
)
--Returns a result set that lists all the employees who report to the
--specific employee directly or indirectly.*/
AS
BEGIN
WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort) AS
(SELECT CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName),
e.Title,
e.EmployeeID,
1,
CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName)
FROM HumanResources.Employee AS e
JOIN Person.Contact AS c ON e.ContactID = c.ContactID
WHERE e.EmployeeID = @InEmpID
UNION ALL
SELECT CONVERT(Varchar(255), REPLICATE ('| ' , EmployeeLevel) +
c.FirstName + ' ' + c.LastName),
e.Title,
e.EmployeeID,
EmployeeLevel + 1,
CONVERT (Varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' +
LastName)
FROM HumanResources.Employee as e
JOIN Person.Contact AS c ON e.ContactID = c.ContactID
JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
)
-- copy the required columns to the result of the function
INSERT @retFindReports
SELECT EmployeeID, Name, Title, EmployeeLevel, Sort
FROM DirectReports
RETURN
END;
GO
-- Example invocation
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM dbo.ufn_FindReports(109)
ORDER BY Sort;
GO
D. CLR 関数を作成する
次の例では、SQL Server データベース エンジン サンプルがローカル コンピューターの既定の場所にインストールされていて、StringManipulate.csproj サンプル アプリケーションがコンパイルされていることを前提としています。詳細については、「Supplementary-Aware String Manipulation」を参照してください。
この例では、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\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf', 'Microsoft SQL Server\90\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 テーブル値関数」を参照してください。
関連項目
参照
その他の技術情報
変更履歴
変更内容 |
|---|
「ベスト プラクティス」に、基になるオブジェクトに対する変更によって関数が古くならないようにするための方法に関する説明を追加しました。 |