CLR スカラー値関数

適用対象:SQL Server

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

CLR スカラー値関数の要件

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

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

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

テーブル値パラメーター

テーブル値パラメーター (TVP) とは、プロシージャや関数に渡されるユーザー定義のテーブル型です。TVP を使用すると、複数行のデータを効率的にサーバーに渡すことができます。 TVP はパラメーター配列と同様の機能を提供しますが、柔軟性が向上し、Transact-SQL との統合が緊密になります。 テーブル値パラメーターを使用するとパフォーマンスが向上する可能性もあります。 また、サーバーへのラウンド トリップを減らすのにも役立ちます。 スカラー パラメーターのリストを使用するなどしてサーバーに複数の要求を送信する代わりに、データを TVP としてサーバーに送信できます。 ユーザー定義テーブル型は、SQL Server プロセスで実行されるマネージド ストアド プロシージャまたは関数に対して、テーブル値パラメーターとして渡したり、その型から返したりすることはできません。 TVP の詳細については、「 Table-Valued パラメーターの使用 (データベース エンジン)」を参照してください。

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  

コードの最初の行は 、Microsoft.SqlServer.Server を参照して属性にアクセスし、 System.Data.SqlClient を参照して ADO.NET 名前空間にアクセスします。 (この名前空間には、SQL Server用の.NET Framework データ プロバイダーである SqlClient が含まれています。

次に、関数は、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 名前空間にある型を使用するクライアント アプリケーションを記述した開発者にとってよく知られているはずです。

[C#]

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();  
}    

[Visual Basic]

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 というファイルに保存すると、次のようにアセンブリとしてコンパイルできます。

[C#]

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

[Visual Basic]

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

Transact-SQL クエリと、アセンブリおよび UDF を登録する呼び出しの例を次に示します。

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 データベース オブジェクトからのデータ アクセス