CLR テーブル値関数

更新 : 2006 年 4 月 14 日

TVF (テーブル値関数) とは、テーブルを返すユーザー定義関数です。

Microsoft SQL Server 2005 では、TVF の機能が拡張され、TVF をどのマネージ言語でも定義できるようになりました。TVF からはデータが IEnumberable オブジェクトまたは IEnumerator オブジェクトを経由して返されます。

メモ :
TVF で返されるテーブル型の列には、timestamp 型の列および Unicode 以外の文字列データ型の列 (char、varchar、text など) を含めることはできません。NOT NULL 制約はサポートされません。

Transact-SQL と CLR のテーブル値関数の違い

Transact-SQL の TVF は、関数の呼び出し結果を具体化して中間テーブルを作成します。TVF では中間テーブルを使用するため、結果に対する制約や一意インデックスがサポートされます。これらの機能は、大量の結果が返される場合に非常に有用です。

一方、CLR の TVF は同じことをストリーミングで実現します。結果セット全体を 1 つのテーブルに具体化する必要はありません。TVF を呼び出すクエリの実行プランから、マネージ関数が返す IEnumerable オブジェクトを直接呼び出し、結果を増分方式で使用します。このストリーミング モデルでは、テーブル全体に値が格納されるまで待たなくても、最初の行が生成された直後から結果を使用できます。返される行をメモリ内で一括して具体化する必要がないので、返される行数が多い場合にもストリーミングが適しています。たとえば、マネージ TVF を使用して、テキスト ファイルを解析し、テキストの各行を 1 つのテーブル行にして返すことができます。

テーブル値関数の実装

TVF は、Microsoft .NET Framework アセンブリのクラスのメソッドとして実装します。TVF コードには IEnumerable インターフェイスを実装する必要があります。IEnumerable インターフェイスは .NET Framework で定義されています。.NET Framework で配列およびコレクションを表す型は、既に IEnumerable インターフェイスを実装しています。このため、コレクションまたは配列を結果セットに変換する TVF を簡単に記述できます。

出力パラメータとテーブル値関数

出力パラメータを使用して TVF から情報を返すことができます。実装コードの TVF の対応するパラメータは、参照渡しのパラメータを引数として使用します。Visual Basic は出力パラメータを Visual C# と同様にはサポートしていません。次に示すように、パラメータを参照渡しで指定し、出力パラメータを示す <Out()> 属性を付ける必要があります。

Visual Basic

Imports System.Runtime.InteropServices
…
Public Shared Sub FillRow ( <Out()> ByRef value As SqlInt32)

Transact-SQL での TVF の定義

CLR の TVF を定義するための構文は、Transact-SQL の TVF の構文と似ていますが、EXTERNAL NAME 句が付加されます。次に例を示します。

CREATE FUNCTION GetEmpFirstLastNames()
RETURNS TABLE (FirstName NVARCHAR(4000), LastName NVARCHAR(4000))
EXTERNAL NAME MyDotNETAssembly.[MyNamespace.MyClassname]. GetEmpFirstLastNames;

TVF を使用して、クエリで追加処理を行うリレーショナル形式のデータを表現できます。次に例を示します。

select * from function();
select * from tbl join function() f on tbl.col = f.col;
select * from table t cross apply function(t.column);

TVF は次の場合にテーブルを返すことができます。

• スカラ値の入力引数から作成された場合。たとえば、数値をコンマで区切った文字列を変換してテーブルにする TVF などです。
• 外部データから生成した場合。たとえば、イベント ログを読み取り、テーブルとして公開する TVF などです。

注   TVF は、FillRow メソッドではなく InitMethod メソッドで Transact-SQL クエリを使用した場合に限り、データ アクセスを実行できます。Transact-SQL クエリを実行する場合は、InitMethod を SqlFunction.DataAccess.Read 属性プロパティに指定してください。

テーブル値関数のサンプル

次の TVF はシステム イベント ログから情報を返します。読み取るイベント ログの名前を含んだ文字列引数を 1 つ受け取ります。

サンプル コード

[C#]

using System;
using System.Data.Sql;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Data.SqlTypes;
using System.Diagnostics;

public class TabularEventLog
{
    [SqlFunction(FillRowMethodName = "FillRow")]
    public static IEnumerable InitMethod(String logname)
    {
        return new EventLog(logname, Environment.MachineName).Entries;
    }

    public static void FillRow(Object obj, out SqlDateTime timeWritten, out SqlChars message, out SqlChars category, out long instanceId)
    {
        EventLogEntry eventLogEntry = (EventLogEntry)obj;
        timeWritten = new SqlDateTime(eventLogEntry.TimeWritten);
        message = new SqlChars(eventLogEntry.Message);
        category = new SqlChars(eventLogEntry.Category);
        instanceId = eventLogEntry.InstanceId;
    }
}

[Visual Basic]

Imports System
Imports System.Data.Sql
Imports Microsoft.SqlServer.Server
Imports System.Collections
Imports System.Data.SqlTypes
Imports System.Diagnostics
Imports System.Runtime.InteropServices

Public Class TabularEventLog
    <SqlFunction(FillRowMethodName:="FillRow")> _
    Public Shared Function InitMethod(ByVal logname As String) As IEnumerable
        Return New EventLog(logname, Environment.MachineName).Entries
    End Function

    Public Shared Sub FillRow(ByVal obj As Object, <Out()> ByRef timeWritten As SqlDateTime, <Out()> ByRef message As SqlChars, <Out()> ByRef category As SqlChars, <Out()> ByRef instanceId As Long)
        Dim eventLogEnTry As EventLogEntry = CType(obj, EventLogEntry)
        timeWritten = New SqlDateTime(eventLogEnTry.TimeWritten)
        message = New SqlChars(eventLogEnTry.Message)
        category = New SqlChars(eventLogEnTry.Category)
        instanceId = eventLogEnTry.InstanceId
    End Sub
End Class

サンプル TVF の宣言および使用

コンパイルしたサンプル TVF は、次のように Transact-SQL で宣言できます。

CREATE ASSEMBLY tvfEventLog 
FROM'D:\assemblies\tvfEventLog\tvfeventlog.dll' 
WITH PERMISSION_SET = SAFE
GO
CREATE FUNCTION ReadEventLog(@logname nvarchar(100))
RETURNS TABLE 
(logTime datetime,Message nvarchar(4000),Category nvarchar(4000),InstanceId bigint)
AS 
EXTERNAL NAME tvfEventLog.TabularEventLog.InitMethod
GO

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

メモ :
テーブル値関数を含むデータベース オブジェクトなど、Visual C++ の /clr:pure コンパイラ オプションを指定してコンパイルした Visual C++ のマネージ データベース オブジェクトは、SQL Server 2005 では実行できません。

サンプルをテストするには、次の Transact-SQL コードを使用してください。

-- select the top 100 events
SELECT TOP 100 *
FROM dbo.ReadEventLog(N'Security') as T
go

-- select the last 10 login events
SELECT TOP 10 T.logTime, T.Message, T.InstanceId 
FROM dbo.ReadEventLog(N'Security') as T
WHERE T.Category = N'Logon/Logoff'
go

参照

その他の技術情報

データベース互換性レベル オプション

ヘルプおよび情報

SQL Server 2005 の参考資料の入手

変更履歴