テーブル値パラメータについて少し整理とメモ

SQL Serverに対してアプリケーションから複数行のデータを渡す時に利用できる仕組みとして、テーブル値パラメータ (Table-Valued Parameters / TVPs) があります。『アプリケーションからクエリを発行する際に配列を渡したい』と思った時に等にも利用できる、便利な仕組みです。

これはSQL Server 2008から実装されている仕組みなのですが、私はここ数年はOracleばかりでこの仕組みをあまり把握していませんでした。ということで、少し調べてメモしておくことにします。

基本的に、情報の一次ソースはこのあたりです（※本エントリはあくまで私個人のメモですのです。正確な情報が欲しい方は一次ソースをあたってください）。

テーブル値パラメータとは

テーブル値パラメータの概要を箇条書きにしてみました。

『複数行のデータ』をクライアントアプリケーションから SQL Server へ渡すための機構
「ユーザー定義テーブル型」を定義し、これをパラメータとして利用する仕組み
SQL Server 2008から実装されている
戻り値やOUTPUT引数としては利用できない
サイズに明示的な上限はなく、環境のメモリに依存する
引数として渡されたT-SQL内（ストアドプロシージャや関数内）では通常のテーブルと同じように読みだすことができる
以下の制限がある
- テーブル値パラメータをCLR ユーザー定義の関数に渡すことはできない
- テーブル値パラメータに対するインデックスはUNIQUE 制約または PRIMARY KEY 制約を付与するためのみ付与することでできる
  - 統計情報は保持しない
- テーブル値パラメーターは Transact-SQL コードの中では読み取り専用
- ALTER TABLEで定義を変更することはできない

あくまでも『アプリケーションというSQL Serverの外の世界から、まとまったデータをSQL Serverに対して渡す』ための機構、ということだと思います。

ADO.NETでのテーブル値パラメータの扱いについて

アプリケーションからSQL Serverに対してデータを渡すためには、何等かミドルウェアなりドライバなりが必要になります。.NETのアプリケーションの場合はADO.NETのSystem.Data.SqlClientが標準的なそれにあたります。

通常、System.Data.SqlClient においてSqlCommandにパラメータを渡すにはSqlParameterを作成してセットするわけですが、テーブル値パラメータでも同様です。この時SqlParameter のValueとして使用できる型は以下の模様。

DbDataReader（の派生オブジェクト）
DataTable
System.Collections.Generic.IEnumerable<SqlDataRecord>

他のデータソースからADO.NETで読み込んだ値そのまま渡す場合は、DbDataReaderでストリーミングとして渡すことが可能。例えばOracleデータベースから読みだした値をOracleDataReaderで受けてそのまま渡したり、Excelから読み込んだ値をOleDbDataReaderで受けて流し込んだりできる模様です。

それ以外の場合、たとえばアプリケーション内のその他のオブジェクトとして保持しているデータを渡したい場合は、DataTableかIEnumerable;SqlDataRecord>の形にしてから渡すことになります。

またSqlParameter のプロパティとして以下を設定しておく必要があるようです。

SqlDbType : SqlDbType.Structured
TypeName : "<ユーザー定義テーブル型の型名>"*1

このあたりの例とサンプルは、前述の『テーブル値パラメーター(ADO.NET) 』に詳しく書かれています。

System.Data.SqlClientでのサンプルコード

自分でも触ってみないとよくわからないので、サンプルコードで試してみたいと思います。とりあえず『単純な配列をパラメータとして渡したい』という想定で、以下を試してみました。

SELECT
ストアドプロシージャ
スカラ値関数
テーブル値関数

サンプルDBのオブジェクトの定義

サンプルDBには適当に以下のようなオブジェクトを定義します*2。

-- テーブル
CREATE TABLE [dbo] .[Users] (
    [Id]   INT            IDENTITY ( 1 , 1 ) NOT NULL,
    [Name] NVARCHAR ( 50 ) NOT NULL,
    [Age]  INT           NOT NULL,
    PRIMARY KEY CLUSTERED ( [Id] ASC )
);

-- ユーザー定義テーブル型
CREATE TYPE [dbo] .[IntValueTableType] AS TABLE (
    [Value] INT NULL);

-- ストアドプロシージャ
CREATE PROCEDURE [dbo] .[SummaryValueProc]
    @Values IntValueTableType READONLY
AS
BEGIN
    SELECT SUM ( Value) AS Summary FROM @Values;
    RETURN 0;
END ;

-- テーブル値関数
CREATE FUNCTION [dbo] .[TransformValue]
(
  @Values IntValueTableType READONLY
)
RETURNS TABLE AS RETURN
(
  SELECT ( Value * Value ) AS Value FROM @Values
)

-- スカラ値関数
CREATE FUNCTION [dbo] .[SummaryValue]
(
  @Values IntValueTableType READONLY
)
RETURNS INT
AS
BEGIN
  DECLARE @sum int ;
  SELECT @sum = SUM( value ) from @Values ;
  RETURN @sum;
END

テーブルには適当に数行、レコードを追加しておきます（なんでもいいです）。

テーブル値パラメータ設定用のメソッドを定義

初めに、テーブル値パラメータをDataTableとして組み立てるメソッドをこんな感じで定義しておきます。

private System.Data. DataTable createValuesTvp(int [] values)
{
  var dt = new System.Data.DataTable ();
  dt.Columns.Add( "Values" , typeof(int));
  foreach (var value in values)
  {
    var row = dt.NewRow();
    row[0] = value;
    dt.Rows.Add(row);
  }
  dt.EndLoadData();
  return dt;
}

SELECT文

まずは単純にSELECT文で使ってみます。SQL文中に『@』をつけて、普通のパラメタライズドクエリとして書きます。SQL文中でのテーブル値パラメータの部分は、テーブルと同じ扱いで書きます。

// 配列からTVPsとして渡すためのDataTableを作っておく
var values = new int[] { 1, 2 };
var tvp = createValuesTvp(values);

using (var con = new SqlConnection(connectionString))
{
  con.Open();

  var sql = "SELECT Id, Name, Age FROM Users WHERE Id IN (SELECT Value FROM @tvp);" ;
  var cmd = new SqlCommand (sql, con);
  var param = cmd.Parameters.AddWithValue("@tvp" , tvp);
  param.SqlDbType = SqlDbType.Structured;
  param.TypeName = "dbo.IntValueTableType";

  using ( var dr = cmd.ExecuteReader())
  {
    while (dr.Read())
    {
      // 読みだす
    }
  }
}

ストアドプロシージャ

次はストアドを実行してみました。なんとなくレコードを返すストアドにしていますが、もちろん必須ではありません。パラメータとして設定する名前はストアド型定義している名前と一致させる必要があります（通常、ストアドを実行するのと同じです）。

var values = new int[] { 1, 2 };
var tvp = createValuesTvp(values);

using (var con = new SqlConnection(connectionString))
{
  con.Open();

  var sql = "dbo.SummaryValueProc" ;
  var cmd = new SqlCommand (sql, con);
  cmd.CommandType = CommandType.StoredProcedure;
  var param = cmd.Parameters.AddWithValue("@Values" , tvp); // ストアド側のパラメータとして定義した名前
  param.SqlDbType = SqlDbType.Structured;
  param.TypeName = "dbo.IntValueTableType"; //【追記】無くても動く

  using ( var dr = cmd.ExecuteReader())
  {
    while (dr.Read())
    {
      // 読みだす
    }
  }
}

【4/5 追記】

Dapper周りのところで調べていて気が付いたのですが、ストアドの場合はTypeNameを設定しなくても問題なく動作するようです（MSDNのサンプルでも記述していなかった）。たぶんですが、ストアドの引数の定義側で型情報を持っているから……とかじゃないかと思います（未確認）。

スカラ値関数

スカラ値関数のパラメータとして渡す場合も特に問題なく渡せました。普通にSELECT文で利用するのと同じです。注意点は、パラメータオブジェクトに設定する名前は、あくまでこちらのSELECT文のクエリで定義した名前に合わせる（＝関数定義側のパラメータ定義の名前とは関係ない）という点くらいでしょうか。

var values = new int[] { 1, 2 };
var tvp = createValuesTvp(values);

using (var con = new SqlConnection(connectionString))
{
  con.Open();

  var sql = "SELECT dbo.SummaryValue(@tvp) AS Summary;" ;
  var cmd = new SqlCommand (sql, con);
  var param = cmd.Parameters.AddWithValue("@tvp" , tvp);
  param.SqlDbType = System.Data. SqlDbType .Structured;
  param.TypeName = "dbo.IntValueTableType";

  using ( var dr = cmd.ExecuteReader())
  {
    while (dr.Read())
    {
      // 読みだす
    }
  }
}

テーブル値関数

これも特に変わったとことはありませんね。普通に読めました。

var values = new int[] { 1, 2 };
var tvp = createValuesTvp(values);

using (var con = new SqlConnection(connectionString))
{
  con.Open();

  var sql = "SELECT Value FROM dbo.TransformValue(@tvp);" ;
  var cmd = new SqlCommand (sql, con);
  var param = cmd.Parameters.AddWithValue("@tvp" , tvp);
  param.SqlDbType = System.Data. SqlDbType .Structured;
  param.TypeName = "dbo.IntValueTableType";

  using ( var dr = cmd.ExecuteReader())
  {
    while (dr.Read())
    {
      // 読みだす
    }
  }
}