CLR スカラー値関数

適用対象:SQL Server

スカラー値関数 (SVF) は、文字列、整数、ビット値などの 1 つの値を返します。 任意の .NET Framework プログラミング言語を使用して、マネージド コードでスカラー値のユーザー定義関数を作成できます。 これらの関数は、Transact-SQL またはその他のマネージド コードからアクセスできます。 共通言語ランタイム (CLR) 統合の利点とマネージド コードと Transact-SQL の選択については、「CLR 統合の概要 を参照してください。

CLR スカラー値関数の要件

.NET Framework SVF は、.NET Framework アセンブリのクラスのメソッドとして実装されます。 SVF から返される入力パラメーターと型には、SQL Server でサポートされている任意のスカラー データ型を指定できます。 varchar , char, rowversion, テキスト, ntext, image, timestamp, table, カーソルを除く。 SV は、SQL Server データ型と実装メソッドの戻りデータ型の間で一致することを確認する必要があります。 型変換の詳細については、「CLR パラメーター データのマップ」を参照してください。

.NET Framework 言語で .NET Framework SVF を実装する場合は、SqlFunction カスタム属性を指定して、関数に関する追加情報を含めることができます。 SqlFunction 属性は、関数がデータにアクセスまたは変更するかどうか、決定論的な場合、および関数に浮動小数点演算が含まれるかどうかを示します。

スカラー値のユーザー定義関数は、決定論的または非決定論的である可能性があります。 決定論的関数は、特定の入力パラメーターのセットを使用して呼び出されると、常に同じ結果を返します。 非決定論的関数は、特定の入力パラメーターのセットを使用して呼び出されると、異なる結果を返す場合があります。

Note

同じ入力値と同じデータベース状態を指定して、関数が常に同じ出力値を生成するとは限らない場合は、関数を決定論的としてマークしないでください。 完全に決定的ではない関数を決定的関数としてマークした場合、インデックス付きビューと計算列が破損する可能性があります。 IsDeterministic プロパティを true に設定して、関数を決定論的としてマークします。

テーブル値パラメーター

テーブル値パラメーター (TVP) とは、プロシージャや関数に渡されるユーザー定義のテーブル型です。TVP を使用すると、複数行のデータを効率的にサーバーに渡すことができます。 TVP はパラメーター配列と同様の機能を提供しますが、Transact-SQL との柔軟性が向上し、より緊密に統合できます。 テーブル値パラメーターを使用するとパフォーマンスが向上する可能性もあります。

また、サーバーへのラウンド トリップを減らすのにも役立ちます。 スカラー パラメーターのリストを使用するなどしてサーバーに複数の要求を送信する代わりに、データを TVP としてサーバーに送信できます。 ユーザー定義テーブル型は、SQL Server プロセスで実行されるマネージド ストアド プロシージャまたは関数に対して、テーブル値パラメーターとして渡したり、そのパラメーターから返したりすることはできません。 TVP の詳細については、「テーブル値パラメーターの使用 (データベース エンジン)を参照してください。

CLR スカラー値関数の例

データにアクセスし、整数値を返す単純な SVF を次に示します。

using Microsoft.SqlServer.Server;
using System.Data.SqlClient;

public class T
{
    [SqlFunction(DataAccess = DataAccessKind.Read)]
    public static int ReturnOrderCount()
    {
        using (SqlConnection conn
            = new SqlConnection("context connection=true"))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(
                "SELECT COUNT(*) AS 'Order Count' FROM SalesOrderHeader", conn);
            return (int)cmd.ExecuteScalar();
        }
    }
}

Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient

Public Class T
    <SqlFunction(DataAccess:=DataAccessKind.Read)> _
    Public Shared Function ReturnOrderCount() As Integer
        Using conn As New SqlConnection("context connection=true")
            conn.Open()
            Dim cmd As New SqlCommand("SELECT COUNT(*) AS 'Order Count' FROM SalesOrderHeader", conn)
            Return CType(cmd.ExecuteScalar(), Integer)
        End Using
    End Function
End Class

コードの最初の行は、ADO.NET 名前空間にアクセスするための属性と System.Data.SqlClient にアクセスするための Microsoft.SqlServer.Server を参照します。 (この名前空間には、SqlClient、.NET Framework Data Provider for SQL Server が含まれています)。

次に、Microsoft.SqlServer.Server 名前空間にある SqlFunction カスタム属性を受け取ります。 このカスタム属性は、UDF (ユーザー定義関数) がサーバーのデータを読み取るときにインプロセス プロバイダーを使用するかどうかを示します。 SQL Server では、UDF によるデータの更新、挿入、または削除は許可されません。 SQL Server では、インプロセス プロバイダーを使用しない UDF の実行を最適化できます。 これは、DataAccessKind を DataAccessKind.Noneに設定することによって示されます。 その次の行で、対象のメソッドは public static (Visual Basic .NET では shared) になっています。

Microsoft.SqlServer.Server 名前空間にある SqlContext クラスは、既に設定されている SQL Server インスタンスへの接続を持つ SqlCommand オブジェクトにアクセスできます。 ここでは使用しませんが、現在のトランザクション コンテキストは、System.Transactions アプリケーション プログラミング インターフェイス (API) を介して使用することもできます。

関数本体のコード行のほとんどは、System.Data.SqlClient 名前空間にある型を使用するクライアント アプリケーションを記述する開発者にとってよく知られているはずです。

using(SqlConnection conn = new SqlConnection("context connection=true"))
{
   conn.Open();
   SqlCommand cmd = new SqlCommand(
        "SELECT COUNT(*) AS 'Order Count' FROM SalesOrderHeader", conn);
   return (int) cmd.ExecuteScalar();
}

Using conn As New SqlConnection("context connection=true")
   conn.Open()
   Dim cmd As New SqlCommand( _
        "SELECT COUNT(*) AS 'Order Count' FROM SalesOrderHeader", conn)
   Return CType(cmd.ExecuteScalar(), Integer)
End Using

適切なコマンド テキストは、SqlCommand オブジェクトを初期化することによって指定されます。 前の例では、テーブル SalesOrderHeader内の行数をカウントします。 次に、cmd オブジェクトの ExecuteScalar メソッドが呼び出されます。 これにより、クエリに基づいて int 型の値が返されます。 最後に、注文数が呼び出し側に返されます。

このコードを FirstUdf.cs というファイルに保存すると、次のようにアセンブリとしてコンパイルできます。

csc.exe /t:library /out:FirstUdf.dll FirstUdf.cs

vbc.exe /t:library /out:FirstUdf.dll FirstUdf.vb

/t:libraryは、実行可能ファイルではなくライブラリを生成することを示しています。 SQL Server に実行可能ファイルを登録することはできません。

/clr:pure でコンパイルされた Visual C++ データベース オブジェクトは、SQL Server での実行ではサポートされていません。 このようなデータベース オブジェクトには、スカラー値関数などがあります。

アセンブリと UDF を登録するための Transact-SQL クエリとサンプル呼び出しは次のとおりです。

CREATE ASSEMBLY FirstUdf
    FROM 'FirstUdf.dll';
GO

CREATE FUNCTION CountSalesOrderHeader()
RETURNS INT
AS EXTERNAL NAME FirstUdf.T.ReturnOrderCount;
GO

SELECT dbo.CountSalesOrderHeader();
GO

Transact-SQL で公開されている関数名は、ターゲットのパブリック静的メソッドの名前と一致する必要はありません。

関連コンテンツ

• CLR パラメーター データ のマップ
• CLR 統合: CLR ルーチンのカスタム属性
• ユーザー定義関数 の
• CLR データベース オブジェクトからのデータ アクセス