Usar parámetros con valores de tabla (motor de base de datos)Use Table-Valued Parameters (Database Engine)

SE APLICA A: síSQL Server síAzure SQL Database noAzure SQL Data Warehouse noAlmacenamiento de datos paralelos APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

Los parámetros con valores de tabla se declaran utilizando tipos de tabla definidos por el usuario.Table-valued parameters are declared by using user-defined table types. Puede utilizar parámetros con valores de tabla para enviar varias filas de datos a una rutina o una instrucción Transact-SQLTransact-SQL , como un procedimiento almacenado o una función, sin crear una tabla temporal o muchos parámetros.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.

Los parámetros con valores de tabla son como las matrices de parámetros en OLE DB y ODBC, pero proporcionan más flexibilidad y una integración más estrecha con 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. Los parámetros con valores de tabla también tienen la ventaja de poder participar en operaciones basadas en conjuntos.Table-valued parameters also have the benefit of being able to participate in set-based operations.

Transact-SQLTransact-SQL pasa parámetros con valores de tabla a rutinas por referencia para evitar la realización de una copia de los datos de entrada.passes table-valued parameters to routines by reference to avoid making a copy of the input data. Puede crear y ejecutar rutinas Transact-SQLTransact-SQL con parámetros con valores de tabla y llamarlas desde código de Transact-SQLTransact-SQL , clientes nativos y administrados en cualquier lenguaje administrado.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.

En este tema:In This Topic:

VentajasBenefits

RestriccionesRestrictions

Parámetros con valores de tabla frente a operaciones BULK INSERTTable-Valued Parameters vs. BULK INSERT Operations

EjemploExample

VentajasBenefits

Un parámetro con valores de tabla está incluido en el ámbito de procedimiento almacenado, función o texto Transact-SQLTransact-SQL dinámico, exactamente igual que los demás parámetros.A table-valued parameter is scoped to the stored procedure, function, or dynamic Transact-SQLTransact-SQL text, exactly like other parameters. Del mismo modo, una variable de tipo de tabla tiene el mismo ámbito que cualquier otra variable local creada mediante una instrucción DECLARE.Similarly, a variable of table type has scope like any other local variable that is created by using a DECLARE statement. Puede declarar variables con valores de tabla en instrucciones Transact-SQLTransact-SQL dinámicas y pasar estas variables como parámetros con valores de tabla a procedimientos almacenados y funciones.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.

Los parámetros con valores de tabla proporcionan más flexibilidad y, en algunos casos, un rendimiento mayor que las tablas temporales u otros medios para pasar una lista de parámetros.Table-valued parameters offer more flexibility and in some cases better performance than temporary tables or other ways to pass a list of parameters. Los parámetros con valores de tabla proporcionan las ventajas siguientes:Table-valued parameters offer the following benefits:

  • No adquieren bloqueos para el rellenado inicial de datos de un cliente.Do not acquire locks for the initial population of data from a client.
  • Proporcionan un modelo de programación simple.Provide a simple programming model.
  • Permiten la inclusión de lógica comercial compleja en una rutina única.Enable you to include complex business logic in a single routine.
  • Reducen los viajes de ida y vuelta al servidor.Reduce round trips to the server.
  • Pueden tener una estructura de tabla de cardinalidad diferente.Can have a table structure of different cardinality.
  • Tienen establecimiento inflexible de tipos.Are strongly typed.
  • Permiten al cliente especificar un criterio de ordenación y claves únicas.Enable the client to specify sort order and unique keys.
  • Se almacenan en caché como una tabla temporal cuando se usa en un procedimiento almacenado.Are cached like a temp table when used in a stored procedure. A partir de SQL Server 2012 (11.x)SQL Server 2012 (11.x), los parámetros con valores de tabla también se almacenan en caché para las consultas con parámetros.Starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x), table-valued parameters are also cached for parameterized queries.

RestriccionesRestrictions

Los parámetros con valores de tabla tienen las restricciones siguientes:Table-valued parameters have the following restrictions:

  • SQL ServerSQL Server no mantiene estadísticas en las columnas de parámetros con valores de tabla.does not maintain statistics on columns of table-valued parameters.
  • Los parámetros con valores de tabla se deben pasar como parámetros READONLY de entrada a rutinas Transact-SQLTransact-SQL .Table-valued parameters must be passed as input READONLY parameters to Transact-SQLTransact-SQL routines. No se pueden realizar operaciones de DML como UPDATE, DELETE o INSERT en un parámetro con valores de tabla en el cuerpo de una rutina.You cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.
  • No se puede utilizar un parámetro con valores de tabla como destino de una instrucción SELECT INTO o INSERT EXEC.You cannot use a table-valued parameter as target of a SELECT INTO or INSERT EXEC statement. Un parámetro con valores de tabla puede estar en la cláusula FROM de SELECT INTO o en el procedimiento almacenado o la cadena INSERT EXEC.A table-valued parameter can be in the FROM clause of SELECT INTO or in the INSERT EXEC string or stored procedure.

Parámetros con valores de tabla frente a operaciones BULK INSERTTable-Valued Parameters vs. BULK INSERT Operations

El uso de parámetros con valores de tabla es comparable a otras formas de uso de variables basadas en conjuntos; sin embargo, el uso de parámetros con valores de tabla puede ser con frecuencia más rápido para grandes conjuntos de datos.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. Si se comparan con operaciones masivas que tienen un costo de inicio mayor que los parámetros con valores de tabla, el comportamiento de los parámetros con valores de tabla es excelente cuando se insertan menos de 1.000 filas.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.

Los parámetros con valores de tabla que se vuelven a utilizar se benefician del almacenamiento en caché de tablas temporales.Table-valued parameters that are reused benefit from temporary table caching. Este almacenamiento en memoria caché de tablas proporciona una escalabilidad mejor que en el caso de operaciones BULK INSERT equivalentes.This table caching enables better scalability than equivalent BULK INSERT operations. Si se usan pequeñas operaciones de inserción de filas, se puede conseguir una ligera mejora del rendimiento utilizando listas de parámetros o instrucciones por lotes en lugar de operaciones BULK INSERT o parámetros con valores de tabla.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. Sin embargo, estos métodos son menos apropiados para programar, y el rendimiento disminuye rápidamente cuando aumentan las filas.However, these methods are less convenient to program, and performance decreases quickly as rows increase.

Los parámetros con valores de tabla se comportan tan bien o mejor que una implementación de matriz de parámetros equivalente.Table-valued parameters perform equally well or better than an equivalent parameter array implementation.

EjemploExample

En el ejemplo siguiente se utiliza Transact-SQLTransact-SQL y se muestra la forma de crear un tipo de parámetro con valores de tabla, declarar una variable para hacer referencia a ella, rellenar la lista de parámetros y, a continuación, pasar los valores a un procedimiento almacenado en la base de datos de 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;

Consulte tambiénSee Also