SQL CLRを極める3つのコーディング・テクニック：SQL Server 2005を使いこなそう（6）（1/3 ページ）

　前回の「SQL CLRコーディング、最初の一歩」では、SQL CLRに関する基本的なコーディングを解説しました。コーディングの容易さや、SQL Server 2005とVisual Studio 2005の綿密な連携による生産性の高さなど、多くの利点を感じ取ることができたはずです。

　今回は、テーブルを返すためのユーザー定義関数であるテーブル値関数（以下、TVF）の解説に加えて、外部データベースとの接続、正規表現の利用などを解説します。

SQL CLRでTVFを使うメリット

　TVFは、テーブルを返すことのできるユーザー定義関数です。前回解説したスカラ値関数（SVF）が単一の値を返す関数であったのに対して、TVFではテーブル構造のデータを返すことができます。

　Transact-SQLでもTVFを作成することができましたが、その内部動作はSQL CLRとは異なります。Transact-SQL TVFでは、結果が中間テーブルに保存され、参照する際はこの中間テーブルを利用します。一方、CLR TVFでは結果がストリーミングとして提供されます。中間テーブルを利用する場合、すべてのデータが中間テーブルにセットされるまでデータを利用することができませんが、ストリーミングモデルの場合、最初の行が利用可能となった時点で直ちに結果を参照できます。従って、膨大な量の行が返される場合はCLR TVFが適しているといえるでしょう。

　TVFは次のようなシーンでの利用が想定されます。

• 入力引数からテーブルを作成する場合（カンマ区切りの文字列を受け取り、テーブルとして返すなど）
• 外部データからテーブルを作成する場合（イベントログを読み取り、テーブルとして返すなど）

　上記以外にも、TVFはストアドプロシージャと異なりFROM句で参照できるメリットがあるため、処理結果をより柔軟に利用することが可能です。

TVFプログラムのサンプル作成

　それでは、早速TVFのサンプルを作成してみましょう。今回のサンプルはカンマ区切りで渡された文字列を分解してテーブル値で返します。サンプル内の接続データベースはSQL Server 2005のサンプルデータベースであるAdventure Worksを利用しています。

using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
    //FillRowMethodName：データを返すメソッドの名前を表す
    //TableDefinition  ：結果のテーブル定義を表す
    //IEnumerable      ：.NET Frameworkで配列およびコレクションを表す型
    [SqlFunction(FillRowMethodName = "FillRow",
                 TableDefinition = "addr NVARCHAR(50)")]
    public static IEnumerable SampleTVF(string str)
    {
        return str.Split(',');
    }
    public static void FillRow(object row, out string str)
    {
        str = (string)row;
    }
}

　コードをデバッグするため、Test.SQLへ下記ステートメントを記述します。

SELECT addr FROM dbo.SampleTVF('tokyo,saitama,tokyo,oosaka')
  GROUP BY addr

　実行した結果はリスト3のとおりです。引数に従って関数よりテーブルが返され、さらに呼び出し側でGROUP BY句によるグループ化が行われていることに注目してください。

addr
-----------
oosaka
saitama
tokyo

　前回解説したストアドプロシージャやスカラ値関数に比べて若干癖のあるコーディングが必要となりますが、一度動きを確認すれば、それほど苦労せず使いこなすことができるはずです。（次ページへ続く）