SQL Serverでテーブル値パラメータ（TVP）をストアドプロシージャに渡す方法｜大量データ処理を効率化する使い方

SQL Server

SQL Serverでは、複数行のデータをストアドプロシージャへまとめて渡したい場合、テーブル値パラメータ（TVP）を利用できます。1件ずつパラメータを渡す方法では処理回数が増えてしまいますが、TVPを使うことでテーブル形式のデータを一括で渡すことが可能になります。

この記事では、SQL Serverのテーブル値パラメータ（TVP）の基本的な仕組みや、ユーザー定義テーブル型の作成方法、ストアドプロシージャへ渡す具体的な利用例について解説します。

SQL Serverのテーブル値パラメータ（TVP）とは

テーブル値パラメータ（Table-Valued Parameter）は、SQL Serverで用意されている機能で、テーブル形式のデータをストアドプロシージャや関数へ渡すために利用します。

通常のパラメータでは、整数や文字列など1つの値しか渡せません。しかし、TVPを利用すると複数行・複数列のデータを1つのパラメータとして渡せます。

例えば、注文情報を100件登録したい場合、通常なら100回INSERT処理を呼び出す必要があります。TVPを利用すると、100件分のデータをテーブルとしてまとめて渡し、ストアドプロシージャ側で一括処理できます。

TVPを利用するメリット

テーブル値パラメータには、大量データを扱う処理で多くのメリットがあります。

  • 複数行データを一度の呼び出しで渡せる
  • ネットワーク通信回数を減らせる
  • ストアドプロシージャ側でJOINやINSERT処理が利用できる
  • 一時テーブルより管理しやすい場合がある

例えば、Webアプリケーションから選択された複数の商品IDをSQL Serverへ渡して処理する場合、カンマ区切り文字列などで渡すよりも、TVPを利用した方が安全で扱いやすくなります。

SQL Serverでユーザー定義テーブル型を作成する

TVPを利用するには、まず渡すデータの形式を定義するユーザー定義テーブル型を作成します。

例えば、商品IDと数量を渡すためのテーブル型を作成する場合は、以下のようになります。

CREATE TYPE ProductListType AS TABLE
(
    ProductId INT,
    Quantity INT
);

このCREATE TYPEによって、SQL Server内で利用可能なテーブル形式の型が作成されます。

作成した型は、通常の変数のように扱うのではなく、主にストアドプロシージャのパラメータとして利用します。

ストアドプロシージャでTVPを受け取る方法

作成したテーブル型は、ストアドプロシージャのパラメータとして指定できます。

例えば、商品情報を登録するストアドプロシージャの場合は以下のように定義します。

CREATE PROCEDURE InsertProducts
    @Products ProductListType READONLY
AS
BEGIN
    INSERT INTO Products(ProductId, Quantity)
    SELECT ProductId, Quantity
    FROM @Products;
END;

ポイントは、TVPのパラメータには必ずREADONLYを指定することです。SQL Serverの仕様上、ストアドプロシージャ内でTVPの内容を直接変更することはできません。

ただし、TVPから別のテーブルへINSERTしたり、SELECTで参照したりすることは可能です。

アプリケーションからTVPを渡す方法

SQL ServerのTVPは、C#などのアプリケーションからも利用できます。

例えば.NETアプリケーションでは、DataTableを作成してSqlParameterとして渡す方法が一般的です。

DataTable table = new DataTable();
table.Columns.Add("ProductId", typeof(int));
table.Columns.Add("Quantity", typeof(int));

table.Rows.Add(1, 5);
table.Rows.Add(2, 10);

このDataTableをSQL Server側のユーザー定義テーブル型と一致させることで、ストアドプロシージャへ複数行データを渡せます。

WebシステムでCSV形式の大量データを登録する処理や、複数選択されたデータを一括更新する処理などで特に効果を発揮します。

TVPを利用するときの注意点

便利なTVPですが、利用時にはいくつか注意点があります。

  • TVPはREADONLYであるため直接更新できない
  • 大量データではインデックス設計を考慮する必要がある
  • SQL Serverのバージョンによって対応状況を確認する

また、数百万件規模のデータを頻繁に渡す場合は、TVPだけではなく一括コピー機能や一時テーブルなど、別の方法が適している場合もあります。

例えば数十件から数千件程度のデータをストアドプロシージャへ渡す用途ではTVPが適していますが、巨大なデータ移行処理では専用のバルク処理を検討するとよいでしょう。

TVPと一時テーブルの使い分け

TVPと一時テーブルは似た用途で使われることがありますが、役割が異なります。

項目 TVP 一時テーブル
主な用途 外部からデータを渡す SQL内部で一時的に利用する
変更 読み取り専用 自由に変更可能
作成方法 ユーザー定義型 CREATE TABLE

アプリケーションからSQL Serverへデータを渡したい場合はTVPが向いており、ストアドプロシージャ内部で加工処理を行いたい場合は一時テーブルが適しています。

まとめ

SQL Serverでは、テーブル値パラメータ（TVP）を利用することで、テーブル形式のデータをストアドプロシージャへ渡すことができます。

TVPを利用するには、ユーザー定義テーブル型を作成し、ストアドプロシージャ側でREADONLYパラメータとして受け取ります。

大量の登録処理や複数データの一括更新では、1件ずつ処理するよりも高速で効率的な設計が可能になります。データ量や処理内容に応じて、TVP・一時テーブル・バルク処理を使い分けることが重要です。

コメント

タイトルとURLをコピーしました