テーブル値パラメーター (データベース エンジン)
テーブル値パラメーターは、SQL Server 2008 の新しいパラメーターの型です。テーブル値パラメーターは、ユーザー定義テーブル型を使用して宣言されます。テーブル値パラメーターを使用すると、一時テーブルまたは多数のパラメーターを作成せずに、ストアド プロシージャや関数などの Transact-SQL ステートメントまたはルーチンに複数行のデータを送信できます。
テーブル値パラメーターは OLE DB や ODBC のパラメーター配列に似ていますが、より柔軟性が高く、Transact-SQL との統合も緊密です。テーブル値パラメーターには、セットベースの操作に使用できるという利点もあります。
.gif "注意") 注 |
|---|
Transact-SQL では、入力データのコピーが作成されないようにするために、テーブル値パラメーターがルーチンに参照渡しされます。 |
テーブル値パラメーターを使用して Transact-SQL ルーチンを作成して実行し、Transact-SQL コード、および任意のマネージ言語のマネージ クライアントとネイティブ クライアントから呼び出すことができます。
Transact-SQL でのテーブル値パラメーターの作成および使用
テーブル値パラメーターには、SQL Server の型とその型を参照するパラメーターの 2 つの主要コンポーネントがあります。テーブル値パラメーターを作成して使用するには、次の手順を実行します。
テーブル型を作成してテーブル構造を定義します。
SQL Server の型を作成する方法の詳細については、「ユーザー定義テーブル型」を参照してください。テーブル構造を定義する方法の詳細については、「CREATE TABLE (Transact-SQL)」を参照してください。
テーブル型のパラメーターを含むルーチンを宣言します。SQL Server ルーチンの詳細については、「CREATE PROCEDURE (Transact-SQL)」および「CREATE FUNCTION (Transact-SQL)」を参照してください。
テーブル型の変数を宣言してテーブル型を参照します。変数を宣言する方法の詳細については、「DECLARE @local_variable (Transact-SQL)」を参照してください。
INSERT ステートメントを使用してテーブル変数を入力します。データを挿入する方法の詳細については、「INSERT と SELECT を使用した行の追加」を参照してください。
テーブル変数を作成して入力したら、変数をルーチンに渡すことができます。
ルーチンの有効範囲が失われると、テーブル値パラメーターは使用できなくなります。型定義は削除されるまで残ります。
SQL Server Native Client でテーブル値パラメーターを使用するには、「テーブル値パラメータ (SQL Server Native Client)」を参照してください。
ADO.NET でテーブル値パラメーターを使用するには、ADO.NET のドキュメントを参照してください。
利点
テーブル値パラメーターは柔軟性が高く、一時テーブルやその他の方法でパラメーターの一覧を渡す場合よりもパフォーマンスが向上することもあります。テーブル値パラメーターには、次の利点があります。
クライアントからのデータを最初に設定する際にロックを取得しません。
単純なプログラミング モデルを提供します。
複雑なビジネス ロジックを 1 つのルーチンに含めることができます。
サーバーへのラウンド トリップが減少します。
基数が異なるテーブル構造を含めることができます。
厳密に型指定されます。
クライアントで並べ替え順序と一意キーを指定できます。
制限
テーブル値パラメーターには、次の制限があります。
SQL Server でテーブル値パラメーターの列の統計が保持されません。
テーブル値パラメーターは、READONLY 入力パラメーターとして Transact-SQL ルーチンに渡す必要があります。ルーチン本体でテーブル値パラメーターに対して UPDATE、DELETE、INSERT などの DML 操作を実行することはできません。
SELECT INTO または INSERT EXEC ステートメントの対象としてテーブル値パラメーターを使用することはできません。テーブル値パラメーターは、SELECT INTO の FROM 句か、INSERT EXEC 文字列またはストアド プロシージャに含めることができます。
スコープ
テーブル値パラメーターの対象範囲はストアド プロシージャ、関数、または動的な Transact-SQL テキストで、他のパラメーターと同じです。同様に、テーブル型の変数の対象範囲も、DECLARE ステートメントを使用して作成される他のローカル変数と同じです。テーブル値変数は、動的な Transact-SQL ステートメント内で宣言でき、テーブル値パラメーターとしてストアド プロシージャおよび関数に渡すことができます。
セキュリティ
テーブル値パラメーターの権限は、CREATE、GRANT、DENY、ALTER、CONTROL、TAKE OWNERSHIP、REFERENCES、EXECUTE、VIEW DEFINITION、および REVOKE の Transact-SQL キーワードを使用することで、SQL Server のオブジェクト セキュリティ モデルに従います。
カタログ ビュー
テーブル値パラメーターに関連付けられている情報を取得するには、sys.parameters (Transact-SQL)、sys.types (Transact-SQL)、および sys.table_types (Transact-SQL) のカタログ ビューに対してクエリを実行します。
テーブル値パラメーターと BULK INSERT 操作
テーブル値パラメーターの使用はセットベースの変数を使用するその他の方法に似ていますが、多くの場合、大規模なデータセットを処理するときは、テーブル値パラメーターを使用する方が短時間で済みます。テーブル値パラメーターよりもスタートアップ コストがかかる一括操作と比較すると、1,000 行未満の行を挿入する場合は、テーブル値パラメーターの方がパフォーマンスが高くなります。
再利用されるテーブル値パラメーターの場合、一時テーブル キャッシュを使用するとメリットがあります。このテーブル キャッシュを使用すると、同等の BULK INSERT 操作よりもスケーラビリティが向上します。少数の行の挿入操作では、BULK INSERT 操作またはテーブル値パラメーターではなく、パラメーター一覧またはバッチにまとめられたステートメントを使用すると、パフォーマンス上の利点が多少得られる場合があります。ただし、これらの方法はプログラミングが容易ではなく、行が増えるとすぐにパフォーマンスが低下します。
テーブル値パラメーターは、同等のパラメーター配列の実装と同様またはそれ以上のパフォーマンスを実現します。
次の表に、使用を推奨するテクノロジを挿入操作の速度に基づいて示します。
データ ソース |
サーバー ロジック |
行数 |
最適なテクノロジ |
|---|---|---|---|
サーバー上のフォーマット済みデータ ファイル |
直接挿入 |
< 1,000 |
BULK INSERT |
サーバー上のフォーマット済みデータ ファイル |
直接挿入 |
> 1,000 |
BULK INSERT |
サーバー上のフォーマット済みデータ ファイル |
複雑 |
< 1,000 |
テーブル値パラメーター |
サーバー上のフォーマット済みデータ ファイル |
複雑 |
> 1,000 |
BULK INSERT |
リモート クライアント プロセス |
直接挿入 |
< 1,000 |
テーブル値パラメーター |
リモート クライアント プロセス |
直接挿入 |
> 1,000 |
BULK INSERT |
リモート クライアント プロセス |
複雑 |
< 1,000 |
テーブル値パラメーター |
リモート クライアント プロセス |
複雑 |
> 1,000 |
テーブル値パラメーター |
例
次の例では、Transact-SQL を使用して、テーブル値パラメーターの型を作成してその型を参照する変数を宣言し、パラメーター一覧を入力して値をストアド プロシージャに渡す方法を示します。
USE AdventureWorks2008R2;
GO
/* Create a table type. */
CREATE TYPE LocationTableType AS TABLE
( LocationName VARCHAR(50)
, CostRate INT );
GO
/* Create a procedure to receive data for the table-valued parameter. */
CREATE PROCEDURE usp_InsertProductionLocation
@TVP LocationTableType READONLY
AS
SET NOCOUNT ON
INSERT INTO [AdventureWorks2008R2].[Production].[Location]
([Name]
,[CostRate]
,[Availability]
,[ModifiedDate])
SELECT *, 0, GETDATE()
FROM @TVP;
GO
/* Declare a variable that references the type. */
DECLARE @LocationTVP
AS LocationTableType;
/* Add data to the table variable. */
INSERT INTO @LocationTVP (LocationName, CostRate)
SELECT [Name], 0.00
FROM
[AdventureWorks2008R2].[Person].[StateProvince];
/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;
GO