CREATE 関数 (Transact-SQL)

2025-06-21

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

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

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

この記事では、.NET Framework CLR の SQL Server への統合について説明します。 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 句の構文。

<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 ]
}

論争

または変更

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

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

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

schema_name

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

function_name

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

関数名の後に括弧を付ける必要があります (パラメーターが指定されていない場合でも)。

@parameter_name

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

関数には、最大 2,100 個のパラメーターを含めることができます。宣言された各パラメータの値は、パラメータのデフォルトが定義されていない限り、関数の実行時にユーザーが指定する必要があります。

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

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

[ type_schema_name. ] parameter_data_type

パラメータのデータ型、および必要に応じて、それが属するスキーマ。 Transact-SQL 関数では、 タイムスタンプ データ型を除く、CLR ユーザー定義型とユーザー定義テーブル型を含むすべてのデータ型が許可されます。 CLR 関数の場合、 text、 ntext、 image、ユーザー定義テーブル型、 およびタイムスタンプ データ型を除く、CLR ユーザー定義型を含むすべてのデータ型が許可されます。非スカラー型 (cursor と table) は、Transact-SQL 関数と CLR 関数のいずれでもパラメーターデータ型として指定できません。

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

SQL Server システムのデータ型の名前を含むスキーマ。
現在のデータベース内の現在のユーザーのデフォルトのスキーマ。
現在のデータベース内の dbo スキーマ。

[ = デフォルト ]

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

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

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

読み取り専用

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

return_data_type

スカラーユーザー定義関数の戻り値。 Transact-SQL 関数では、 タイムスタンプ データ型を除く、CLR ユーザー定義型を含むすべてのデータ型が許可されます。 CLR 関数の場合、 text、 ntext、 image、 timestamp の各データ型を除き、CLR ユーザー定義型を含むすべてのデータ型を使用できます。非スカラー型 cursor と table は、Transact-SQL 関数と CLR 関数のいずれでも戻り値のデータ型として指定できません。

function_body

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

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

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

scalar_expression

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

テーブル

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

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

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

select_stmt

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

注文 (<order_clause>)

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

外部名 <method_specifier>assembly_name.class_name。method_name

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

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

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

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

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

メソッドは静的である必要があります。

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

既定では、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 ] )

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

CLR 関数のテーブルデータ型を定義します。テーブル宣言には、列名とデータ型のみが含まれます。テーブルは常にプライマリファイルグループに配置されます。

NULL |NULL でない

ネイティブにコンパイルされたスカラーユーザー定義関数でのみサポートされます。詳細については、「 In-Memory OLTP のスカラー User-Defined 関数」を参照してください。

ネイティブコンパイル

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

アトミックの始まり

ネイティブコンパイルされたスカラーユーザー定義関数に必要であり、サポートされるのはこれだけです。詳細については、ネイティブプロシージャのアトミックブロックを参照してください。

SCHEMABINDING

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

実行形式

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

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

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

暗号化

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

データベースエンジンが CREATE FUNCTION ステートメントの元のテキストを難読化された形式に変換することを示します。難読化の出力は、カタログビューに直接表示されません。システムテーブルまたはデータベースファイルにアクセスできないユーザーは、難読化されたテキストを取得できません。ただし、データベース管理者の診断接続を介してシステム・テーブルにアクセスするか、データベース・ファイルに直接アクセスできる権限を持つユーザーは、テキストを使用できます。また、デバッガをサーバープロセスにアタッチできるユーザーは、実行時にメモリから元のプロシージャを取得できます。システムメタデータへのアクセスの詳細については、「メタデータの可視性の設定」を参照してください。

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

SCHEMABINDING

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

参照するオブジェクトへの関数のバインドは、次のいずれかのアクションが発生した場合にのみ削除されます。

機能は削除されます。
この関数は、SCHEMABINDING オプションが指定されていない ALTER ステートメントを使用して変更されます。

関数をスキーマバインドできるのは、次の条件に当てはまる場合のみです。

この関数は Transact-SQL 関数です。
ユーザー定義関数と、関数によって参照されるビューもスキーマにバインドされます。
関数によって参照されるオブジェクトは、2 つの部分からなる名前を使用して参照されます。
関数とそれが参照するオブジェクトは、同じデータベースに属します。
CREATE FUNCTION 文を実行したユーザーにはREFERENCES関数が参照するデータベース・オブジェクトに対するパーミッションがあります。

NULL 入力で NULL を返す |NULL 入力で呼び出されました

スカラー関数の 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 ステートメントが優先されます。 OnNULLCall 属性は、CLR テーブル値関数には指定できません。

実行形式

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

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

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

インライン = { ON |オフ }

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

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

<column_definition> ::=

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

column_name

テーブル内の列の名前。列名は、識別子の規則に準拠し、テーブル内で一意である必要があります。 column_name は 1 から 128 文字で構成できます。

data_type

列のデータ型を指定します。 Transact-SQL 関数では、 タイムスタンプを除くすべてのデータ型 (CLR ユーザー定義型を含む) が許可されます。 CLR 関数の場合、text、ntext、image、char、varchar、varchar(max)、timestamp を除く、CLR ユーザー定義型を含むすべてのデータ型が許可されます。非スカラー型のカーソルは、Transact-SQL 関数と CLR 関数のいずれでも列のデータ型として指定できません。

デフォルトの constant_expression

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

照合 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 をデフォルトとして指定することはできません。

アイデンティティ

新しい列が ID 列であることを示します。新しい行がテーブルに追加されると、SQL Server は列に対して一意の増分値を提供します。 ID 列は、通常、テーブルの一意の行識別子として機能するために、 PRIMARY KEY 制約と共に使用されます。 IDENTITY プロパティは、tinyint、smallint、int、bigint、decimal(p,0)、または numeric(p,0) 列に割り当てることができます。テーブルごとに作成できる ID 列は 1 つだけです。バインドされたデフォルトと DEFAULT 制約は、ID 列では使用できません。 シードとインクリメントの両方を指定するか、どちらも指定しない必要があります。どちらも指定しない場合、デフォルトは (1,1) です。

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

種

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

インクリメント

テーブル内の連続するローの シード 値に追加する整数値。

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

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

NULL |NULL でない

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

主キー

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

ユニーク

指定された列または列に対して、ユニーク・インデックスを通じてエンティティー整合性を提供する制約。テーブルには、複数の UNIQUE 制約を設定できます。 UNIQUE CLR テーブル値関数には指定できません。

クラスタ化 |非クラスタ化

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

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

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

確認

列に入力できる値を制限することでドメイン整合性を強制する制約。 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 User-Defined 関数」を参照してください。

注釈

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

相互運用性

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

代入ステートメント。
TRY...CATCH ステートメントを除く Control-of-Flow ステートメント。
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) を使用します。

Von Bedeutung

関数は、決定論的であるためには、 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

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

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

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

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

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

メタデータ

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

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

権限

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

例示

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

Ａ。 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 データベース内のインラインテーブル値関数を返します。 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 Table-Valued 関数」を参照してください。

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 関数 (Transact-SQL)

構文

論争

または変更

schema_name

function_name

@parameter_name

[ type_schema_name. ] parameter_data_type

[ = デフォルト ]

読み取り専用

return_data_type

function_body

scalar_expression

テーブル

select_stmt

注文 (<order_clause>)

外部名 <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 |NULL でない

ネイティブコンパイル

アトミックの始まり

SCHEMABINDING

実行形式

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

暗号化

SCHEMABINDING

NULL 入力で NULL を返す |NULL 入力で呼び出されました

実行形式

インライン = { ON |オフ }

<column_definition> ::=

column_name

data_type

デフォルトの constant_expression

照合 collation_name

ROWGUIDCOL

アイデンティティ

種

インクリメント

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

NULL |NULL でない

主キー

ユニーク

クラスタ化 |非クラスタ化

確認

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 テーブル値関数で並べ替え順序を使用する

メタデータ

権限

例示

Ａ。 ISO 週を計算するスカラー値のユーザー定義関数を使用します

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

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

D. CLR 関数を作成する

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

関連コンテンツ

フィードバック

その他のリソース

ベストプラクティス

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

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