使用表值参数(数据库引擎)Use Table-Valued Parameters (Database Engine)

适用于: 是SQL Server是Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库APPLIES 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.
  • 允许在单个例程中包括复杂的业务逻辑。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. 大容量操作的启动开销比表值参数大,与之相比,表值参数在插入数目少于 1000 的行时具有很好的执行性能。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

下面的示例使用 Transact-SQLTransact-SQL 并展示了如何执行以下操作:创建表值参数类型,声明变量来引用它,填充参数列表,然后将值传递到 AdventureWorks 数据库中的存储过程。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