CLR スカラー値関数

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

CLR スカラー値関数の要件

.NET Framework SVF は、.NET Framework アセンブリのクラスのメソッドとして実装されます。 SVF から返される入力パラメーターと型には、または を除きvarcharntextrowversioncharimagetimestamptexttable、SQL Serverでサポートされるスカラー データ型のいずれかを指定できます。cursor 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  

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

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

名前空間にある クラスはSqlContext、既にMicrosoft.SqlServer.Server設定されている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 内の行数をカウントしています。 次に、ExecuteScalar オブジェクトの cmd メソッドが呼び出されます。 これにより、クエリに基づいて 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 データベース オブジェクトからのデータ アクセス