テーブル値パラメーター (データベース エンジン) を使用する
適用対象: 
SQL Server Azure SQL データベース Azure SQL Managed Instance
テーブル値パラメーターは、ユーザー定義テーブル型を使用して宣言されます。 テーブル値パラメーターを使用すると、一時テーブルまたは多数のパラメーターを作成せずに、ストアド プロシージャや関数などの Transact-SQL ステートメントまたはルーチンに複数行のデータを送信できます。
テーブル値パラメーターは OLE DB や ODBC のパラメーター配列に似ていますが、より柔軟性が高く、Transact-SQL との統合も緊密です。 テーブル値パラメーターには、セットベースの操作に使用できるという利点もあります。
Transact-SQL では、入力データのコピーが作成されないようにするために、テーブル値パラメーターがルーチンに参照渡しされます。 テーブル値パラメーターを使用して Transact-SQL ルーチンを作成して実行し、Transact-SQL コード、任意のマネージド言語のマネージド クライアントとネイティブ クライアントから呼び出すことができます。
メリット
テーブル値パラメーターの対象範囲は、ストアド プロシージャ、関数、または動的な Transact-SQL テキストで、他のパラメーターと同じです。 同様に、テーブル型の変数の対象範囲も、DECLARE ステートメントを使用して作成される他のローカル変数と同じです。 テーブル値変数は、動的な Transact-SQL ステートメント内で宣言でき、これらの変数をテーブル値パラメーターとしてストアド プロシージャおよび関数に渡すことができます。
テーブル値パラメーターは柔軟性が高く、一時テーブルやその他の方法でパラメーターの一覧を渡す場合よりもパフォーマンスが向上することもあります。 テーブル値パラメーターには、次の利点があります。
- クライアントからのデータを最初に設定する際にロックを取得しません。
- 単純なプログラミング モデルを提供します。
- 複雑なビジネス ロジックを 1 つのルーチンに含めることができます。
- サーバーへのラウンド トリップが減少します。
- カーディナリティが異なるテーブル構造を含めることができます。
- 厳密に型指定されます。
- クライアントで並べ替え順序と一意キーを指定できます。
- ストアド プロシージャで使用すると、一時テーブルのようにキャッシュされます。 SQL Server 2012 (11.x) 以降のバージョンでは、テーブル値パラメーターもパラメーター化クエリ用にキャッシュされます。
アクセス許可
ユーザー定義テーブル型のインスタンスを作成したり、テーブル値パラメーターを使用してストアド プロシージャを呼び出したりするには、その型に EXECUTE アクセス許可と REFERENCES アクセス許可があるか、その型を含むスキーマまたはデータベースに対してそれらのアクセス許可を持つ必要があります。
制限事項
テーブル値パラメーターには、次の制限があります。
- SQL Server では、テーブル値パラメーターの列の統計は保持されません。
- テーブル値パラメーターは、READONLY 入力パラメーターとして Transact-SQL ルーチンに渡す必要があります。 ルーチン本体でテーブル値パラメーターに対して UPDATE、DELETE、INSERT などの DML 操作を実行することはできません。
SELECT INTOまたはINSERT EXECステートメントの対象としてテーブル値パラメーターを使用することはできません。 テーブル値パラメーターは、FROMのSELECT INTO句か、INSERT EXEC文字列またはストアド プロシージャに含めることができます。
テーブル値パラメーターと BULK INSERT 操作の比較
テーブル値パラメーターの使用はセットベースの変数を使用するその他の方法に似ていますが、多くの場合、大規模なデータセットを処理するときは、テーブル値パラメーターを使用する方が短時間で済みます。 テーブル値パラメーターよりもスタートアップ コストがかかる一括操作と比較すると、1,000 行未満の行を挿入するときにテーブル値パラメーターの方が性能が良くなります。
再利用されるテーブル値パラメーターの場合、一時テーブル キャッシュを使用するとメリットがあります。 このテーブル キャッシュを使用すると、同等の一括挿入操作よりもスケーラビリティが向上します。 少数の行の挿入操作は、BULK INSERT 操作またはテーブル値パラメーターではなく、パラメーター一覧またはバッチ化ステートメントを使用すると、パフォーマンス上の利点が多少得られる場合があります。 ただし、これらの方法はプログラミングが容易ではなく、行が増えるとすぐにパフォーマンスが低下します。
テーブル値パラメーターは、同等のパラメーター配列の実装と同様またはそれ以上のパフォーマンスを実現します。
例
次の例では、サンプルの AdventureWorks データベースで Transact-SQL を使用し、テーブル値パラメーター型の作成、参照する変数の宣言、パラメーター リストを入力した後、値をストアド プロシージャに渡す方法を示します。
/* 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 dbo. usp_InsertProductionLocation
@TVP LocationTableType READONLY
AS
SET NOCOUNT ON
INSERT INTO AdventureWorks2022.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 AdventureWorks2022.Person.StateProvince;
/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;
予想される結果セットは次のとおりです。
(181 rows affected)