Table-Valued パラメーターの使用 (データベースエンジン)

2017-06-13

テーブル値パラメーターは、ユーザー定義テーブル型を使用して宣言されます。テーブル値パラメーターを使用すると、一時テーブルまたは多数のパラメーターを作成せずに、ストアドプロシージャや関数などの Transact-SQL ステートメントまたはルーチンに複数行のデータを送信できます。

テーブル値パラメーターは OLE DB や ODBC のパラメーター配列に似ていますが、より柔軟性が高く、Transact-SQL との統合も緊密です。テーブル値パラメーターには、セットベースの操作に使用できるという利点もあります。

Transact-SQL では、入力データのコピーが作成されないようにするために、テーブル値パラメーターがルーチンに参照渡しされます。テーブル値パラメーターを使用して Transact-SQL ルーチンを作成して実行し、Transact-SQL コード、任意のマネージド言語のマネージドクライアントとネイティブクライアントから呼び出すことができます。

このトピックの内容

特典

制限事項

Table-Valued パラメーター対 BULK INSERT 操作

例

メリット

テーブル値パラメーターの対象範囲は、ストアドプロシージャ、関数、または動的な Transact-SQL テキストで、他のパラメーターと同じです。同様に、テーブル型の変数の対象範囲も、DECLARE ステートメントを使用して作成される他のローカル変数と同じです。テーブル値変数は、動的な Transact-SQL ステートメント内で宣言でき、これらの変数をテーブル値パラメーターとしてストアドプロシージャおよび関数に渡すことができます。

テーブル値パラメーターは柔軟性が高く、一時テーブルやその他の方法でパラメーターの一覧を渡す場合よりもパフォーマンスが向上することもあります。テーブル値パラメーターには、次の利点があります。

クライアントからのデータを最初に設定する際にロックを取得しません。
単純なプログラミングモデルを提供します。
複雑なビジネスロジックを 1 つのルーチンに含めることができます。
サーバーへのラウンドトリップが減少します。
カーディナリティが異なるテーブル構造を含めることができます。
厳密に型指定されます。
クライアントで並べ替え順序と一意キーを指定できます。
ストアドプロシージャで使用すると、一時テーブルのようにキャッシュされます。 SQL Server 2012 以降では、パラメーター化されたクエリ用にテーブル値パラメーターもキャッシュされます。

制約

テーブル値パラメーターには、次の制限があります。

SQL Server では、テーブル値パラメーターの列の統計は保持されません。
テーブル値パラメーターは、READONLY 入力パラメーターとして Transact-SQL ルーチンに渡す必要があります。ルーチン本体でテーブル値パラメーターに対して UPDATE、DELETE、INSERT などの DML 操作を実行することはできません。
SELECT INTO ステートメントまたは INSERT EXEC ステートメントのターゲットとして、テーブル値パラメーターを使用することはできません。テーブル値パラメーターは、SELECT INTO の FROM 句、または INSERT EXEC 文字列またはストアドプロシージャに含めることができます。

Table-Valued パラメーター対BULK INSERT操作

テーブル値パラメーターの使用はセットベースの変数を使用するその他の方法に似ていますが、多くの場合、大規模なデータセットを処理するときは、テーブル値パラメーターを使用する方が短時間で済みます。テーブル値パラメーターよりも起動コストが高い一括操作と比較すると、テーブル値パラメーターは 1,000 行未満の挿入に適しています。

再利用されるテーブル値パラメーターの場合、一時テーブルキャッシュを使用するとメリットがあります。このテーブルキャッシュにより、同等の BULK INSERT 操作よりもスケーラビリティが向上します。小さな行挿入操作を使用することで、BULK INSERT 操作やテーブル値パラメーターではなく、パラメーターリストやバッチステートメントを使用することで、パフォーマンスの向上が期待できるかもしれません。ただし、これらの方法はプログラミングが容易ではなく、行が増えるとすぐにパフォーマンスが低下します。

テーブル値パラメーターは、同等のパラメーター配列の実装と同様またはそれ以上のパフォーマンスを実現します。

例

次の例では、Transact-SQL を使用し、テーブル値パラメーター型を作成し、それを参照する変数を宣言し、パラメーターリストに入力してから、ストアドプロシージャに値を渡す方法を示します。

USE AdventureWorks2012;  
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 dbo. usp_InsertProductionLocation  
    @TVP LocationTableType READONLY  
    AS   
    SET NOCOUNT ON  
    INSERT INTO AdventureWorks2012.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 AdventureWorks2012.Person.StateProvince;  
  
/* Pass the table variable data to a stored procedure. */  
EXEC usp_InsertProductionLocation @LocationTVP;  
GO

こちらもご覧ください

型を作成 (Transact-SQL)
DECLARE @local_variable (Transact-SQL)
sys.types (Transact-SQL)
sys.parameters (Transact-SQL)
sys.parameter_type_usages (Transact-SQL)
CREATE PROCEDURE (Transact-SQL)
関数を作成 (Transact-SQL)

次の方法で共有

Table-Valued パラメーターの使用 (データベース エンジン)

メリット

制約

Table-Valued パラメーター対BULK INSERT操作

例

こちらもご覧ください

その他のリソース

Table-Valued パラメーターの使用 (データベースエンジン)