[アーティクル]
04/01/2012

CLR スカラー値関数

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

CLR スカラー値関数の要件

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

.NET Framework 言語で .NET Framework SVF を実装する場合、SqlFunction カスタム属性を指定し、その関数に関する詳細情報を含めることができます。SqlFunction 属性は、その関数がデータへのアクセスや変更を行うかどうか、決定的関数かどうか、浮動小数点演算を必要とするかどうかなどを示します。

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

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

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

テーブル値パラメーター

テーブル値パラメーター (TVP) とは、プロシージャや関数に渡されるユーザー定義のテーブル型です。TVP を使用すると、複数行のデータを効率的にサーバーに渡すことができます。TVP の機能はパラメーター配列に似ていますが、より柔軟性が高く、Transact-SQL との統合も緊密です。TVP を使用するとパフォーマンスが向上する可能性もあります。また、サーバーへのラウンドトリップを減らすのにも役立ちます。スカラーパラメーターのリストを使用するなどしてサーバーに複数の要求を送信する代わりに、データを 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

コードの 1 行目では、属性にアクセスするために Microsoft.SqlServer.Server を参照し、ADO.NET 名前空間にアクセスするために System.Data.SqlClient (この名前空間には、.NET Framework Data Provider for SQL Server の 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

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

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

アセンブリと 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 で公開する関数名は、対象の public static メソッドの名前と一致している必要はありません。

注
SQL Server 2005 以降、互換性レベルが "80" の SQL Server データベースでは、マネージコードでユーザー定義型、ストアドプロシージャ、関数、集計、またはトリガーを作成することはできません。SQL Server のこれらの CLR 統合機能を使用するには、sp_dbcmptlevel (Transact-SQL) ストアドプロシージャを使用してデータベースの互換性レベルを "100" に設定する必要があります。

SQL Server 2005 以降、互換性レベルが "80" の SQL Server データベースでは、マネージコードでユーザー定義型、ストアドプロシージャ、関数、集計、またはトリガーを作成することはできません。SQL Server のこれらの CLR 統合機能を使用するには、sp_dbcmptlevel (Transact-SQL) ストアドプロシージャを使用してデータベースの互換性レベルを "100" に設定する必要があります。

CLR スカラー値関数

CLR スカラー値関数の要件

テーブル値パラメーター

CLR スカラー値関数の例

関連項目

概念

その他の技術情報

その他のリソース