テーブル値パラメーターの使用 (データベース エンジン)Use Table-Valued Parameters (Database Engine)

適用対象: ○SQL Server ○Azure SQL Database XAzure SQL Data Warehouse XParallel Data WarehouseAPPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

テーブル値パラメーターは、ユーザー定義テーブル型を使用して宣言されます。Table-valued parameters are declared by using user-defined table types. テーブル値パラメーターを使用すると、一時テーブルまたは多数のパラメーターを作成せずに、ストアド プロシージャや関数などの Transact-SQLTransact-SQL ステートメントまたはルーチンに複数行のデータを送信できます。You can use table-valued parameters to send multiple rows of data to a Transact-SQLTransact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters.

テーブル値パラメーターは OLE DB や ODBC のパラメーター配列に似ていますが、より柔軟性が高く、Transact-SQLTransact-SQL との統合も緊密です。Table-valued parameters are like parameter arrays in OLE DB and ODBC, but offer more flexibility and closer integration with Transact-SQLTransact-SQL. テーブル値パラメーターには、セットベースの操作に使用できるという利点もあります。Table-valued parameters also have the benefit of being able to participate in set-based operations.

Transact-SQLTransact-SQL では、入力データのコピーが作成されないようにするために、テーブル値パラメーターがルーチンに参照渡しされます。passes table-valued parameters to routines by reference to avoid making a copy of the input data. テーブル値パラメーターを使用して Transact-SQLTransact-SQL ルーチンを作成して実行し、 Transact-SQLTransact-SQL コード、および任意のマネージド言語のマネージド クライアントとネイティブ クライアントから呼び出すことができます。You can create and execute Transact-SQLTransact-SQL routines with table-valued parameters, and call them from Transact-SQLTransact-SQL code, managed and native clients in any managed language.

このトピックの内容In This Topic:

利点Benefits

制限Restrictions

テーブル値パラメーターと BULK INSERT 操作Table-Valued Parameters vs. BULK INSERT Operations

Example

利点Benefits

テーブル値パラメーターの対象範囲はストアド プロシージャ、関数、または動的な Transact-SQLTransact-SQL テキストで、他のパラメーターと同じです。A table-valued parameter is scoped to the stored procedure, function, or dynamic Transact-SQLTransact-SQL text, exactly like other parameters. 同様に、テーブル型の変数の対象範囲も、DECLARE ステートメントを使用して作成される他のローカル変数と同じです。Similarly, a variable of table type has scope like any other local variable that is created by using a DECLARE statement. テーブル値変数は、動的な Transact-SQLTransact-SQL ステートメント内で宣言でき、テーブル値パラメーターとしてストアド プロシージャおよび関数に渡すことができます。You can declare table-valued variables within dynamic Transact-SQLTransact-SQL statements and pass these variables as table-valued parameters to stored procedures and functions.

テーブル値パラメーターは柔軟性が高く、一時テーブルやその他の方法でパラメーターの一覧を渡す場合よりもパフォーマンスが向上することもあります。Table-valued parameters offer more flexibility and in some cases better performance than temporary tables or other ways to pass a list of parameters. テーブル値パラメーターには、次の利点があります。Table-valued parameters offer the following benefits:

  • クライアントからのデータを最初に設定する際にロックを取得しません。Do not acquire locks for the initial population of data from a client.
  • 単純なプログラミング モデルを提供します。Provide a simple programming model.
  • 複雑なビジネス ロジックを 1 つのルーチンに含めることができます。Enable you to include complex business logic in a single routine.
  • サーバーへのラウンド トリップが減少します。Reduce round trips to the server.
  • カーディナリティが異なるテーブル構造を含めることができます。Can have a table structure of different cardinality.
  • 厳密に型指定されます。Are strongly typed.
  • クライアントで並べ替え順序と一意キーを指定できます。Enable the client to specify sort order and unique keys.
  • ストアド プロシージャで使用すると、一時テーブルのようにキャッシュされます。Are cached like a temp table when used in a stored procedure. SQL Server 2012 (11.x)SQL Server 2012 (11.x)以降では、テーブル値パラメーターはパラメーター化クエリ用にもキャッシュされます。Starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x), table-valued parameters are also cached for parameterized queries.

制限Restrictions

テーブル値パラメーターには、次の制限があります。Table-valued parameters have the following restrictions:

  • SQL ServerSQL Server でテーブル値パラメーターの列の統計が保持されません。does not maintain statistics on columns of table-valued parameters.
  • テーブル値パラメーターは、READONLY 入力パラメーターとして Transact-SQLTransact-SQL ルーチンに渡す必要があります。Table-valued parameters must be passed as input READONLY parameters to Transact-SQLTransact-SQL routines. ルーチン本体でテーブル値パラメーターに対して UPDATE、DELETE、INSERT などの DML 操作を実行することはできません。You cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.
  • SELECT INTO または INSERT EXEC ステートメントの対象としてテーブル値パラメーターを使用することはできません。You cannot use a table-valued parameter as target of a SELECT INTO or INSERT EXEC statement. テーブル値パラメーターは、SELECT INTO の FROM 句か、INSERT EXEC 文字列またはストアド プロシージャに含めることができます。A table-valued parameter can be in the FROM clause of SELECT INTO or in the INSERT EXEC string or stored procedure.

テーブル値パラメーターと BULK INSERT 操作Table-Valued Parameters vs. BULK INSERT Operations

テーブル値パラメーターの使用はセットベースの変数を使用するその他の方法に似ていますが、多くの場合、大規模なデータセットを処理するときは、テーブル値パラメーターを使用する方が短時間で済みます。Using table-valued parameters is comparable to other ways of using set-based variables; however, using table-valued parameters frequently can be faster for large data sets. テーブル値パラメーターよりもスタートアップ コストがかかる一括操作と比較すると、1,000 行未満の行を挿入する場合は、テーブル値パラメーターの方がパフォーマンスが高くなります。Compared to bulk operations that have a greater startup cost than table-valued parameters, table-valued parameters perform well for inserting less than 1000 rows.

再利用されるテーブル値パラメーターの場合、一時テーブル キャッシュを使用するとメリットがあります。Table-valued parameters that are reused benefit from temporary table caching. このテーブル キャッシュを使用すると、同等の BULK INSERT 操作よりもスケーラビリティが向上します。This table caching enables better scalability than equivalent BULK INSERT operations. 少数の行の挿入操作では、BULK INSERT 操作またはテーブル値パラメーターではなく、パラメーター一覧またはバッチにまとめられたステートメントを使用すると、パフォーマンス上の利点が多少得られる場合があります。By using small row-insert operations a small performance benefit might be gained by using parameter lists or batched statements instead of BULK INSERT operations or table-valued parameters. ただし、これらの方法はプログラミングが容易ではなく、行が増えるとすぐにパフォーマンスが低下します。However, these methods are less convenient to program, and performance decreases quickly as rows increase.

テーブル値パラメーターは、同等のパラメーター配列の実装と同様またはそれ以上のパフォーマンスを実現します。Table-valued parameters perform equally well or better than an equivalent parameter array implementation.

Example

次の例では、AdventureWorks データベース内で、Transact-SQLTransact-SQL を使用して、テーブル値パラメーターの型を作成してその型を参照する変数を宣言し、パラメーター一覧を入力してから、その値をストアド プロシージャに渡す方法を示します。The following example uses Transact-SQLTransact-SQL and shows you how to create a table-valued parameter type, declare a variable to reference it, fill the parameter list, and then pass the values to a stored procedure in the AdventureWorks database.

/* 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;

参照See Also