Uso de parámetros Table-Valued (Motor de base de datos)

Los parámetros con valores de tabla se declaran utilizando tipos de tabla definidos por el usuario. Puede utilizar parámetros con valores de tabla para enviar varias filas de datos a una rutina o a una instrucción Transact-SQL, como un procedimiento almacenado o una función, sin crear una tabla temporal o muchos parámetros.

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 mayor integración con Transact-SQL. Los parámetros con valores de tabla también tienen la ventaja de poder participar en operaciones basadas en conjuntos.

Transact-SQL pasa parámetros con valores de tabla a rutinas por referencia para evitar hacer una copia de los datos de entrada. Puede crear y ejecutar rutinas Transact-SQL con parámetros de valores de tabla y llamarlas desde el código de Transact-SQL, clientes nativos y administrados en cualquier lenguaje administrado.

En este tema:

Beneficios

Restricciones

Table-Valued parámetros frente a operaciones BULK INSERT

Ejemplo

Ventajas

Un parámetro con valores de tabla está incluido en el ámbito de procedimiento almacenado, función o texto dinámico Transact-SQL, exactamente igual que los demás parámetros. Del mismo modo, una variable de tipo de tabla tiene el mismo ámbito que cualquier otra variable local creada mediante una instrucción DECLARE. Puede declarar variables con valores de tabla en instrucciones Transact-SQL dinámicas y pasar estas variables como parámetros con valores de tabla a procedimientos almacenados y funciones.

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. Los parámetros con valores de tabla proporcionan las ventajas siguientes:

• No adquieren bloqueos para el rellenado inicial de datos de un cliente.

• Proporcionan un modelo de programación simple.

• Permiten la inclusión de lógica comercial compleja en una rutina única.

• Reducen los viajes de ida y vuelta al servidor.

• Pueden tener una estructura de tabla de cardinalidad diferente.

• Tienen establecimiento inflexible de tipos.

• Permiten al cliente especificar un criterio de ordenación y claves únicas.

• Se almacenan en caché como una tabla temporal cuando se usa en un procedimiento almacenado. A partir de SQL Server 2012, los parámetros con valores de tabla también se almacenan en caché para las consultas con parámetros.

Restricciones

Los parámetros con valores de tabla tienen las restricciones siguientes:

• SQL Server no mantiene las estadísticas en las columnas de parámetros con valores de tabla.

• Los parámetros con valores de tabla se deben pasar como parámetros de entrada READONLY a rutinas Transact-SQL. 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.

• No se puede usar un parámetro con valores de tabla como destino de una instrucción SELECT INTO o INSERT EXEC. Un parámetro con valores de tabla puede estar en la cláusula FROM de SELECT INTO o en la cadena INSERT EXEC o en el procedimiento almacenado.

Table-Valued parámetros frente a operaciones BULK INSERT

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. En comparación con las operaciones masivas que tienen un costo de inicio mayor que los parámetros con valores de tabla, los parámetros con valores de tabla funcionan bien para insertar menos de 1000 filas.

Los parámetros con valores de tabla que se vuelven a utilizar se benefician del almacenamiento en caché de tablas temporales. Este almacenamiento en caché de tablas permite una mejor escalabilidad que las operaciones DE BULK INSERT equivalentes. Mediante el uso de operaciones de inserción de filas pequeñas, se puede obtener una pequeña ventaja de rendimiento mediante listas de parámetros o instrucciones por lotes en lugar de operaciones BULK INSERT o parámetros con valores de tabla. Sin embargo, estos métodos son menos apropiados para programar, y el rendimiento disminuye rápidamente cuando aumentan las filas.

Los parámetros con valores de tabla se comportan tan bien o mejor que una implementación de matriz de parámetros equivalente.

Ejemplo

En el ejemplo siguiente se usa Transact-SQL y se muestra cómo 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.

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  

Véase también

CREATE TYPE (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)
CREATE FUNCTION (Transact-SQL)