Usar parámetros con valores de tabla (motor de base de datos)

  • Artículo

Se aplica a: SQL Server Azure SQL Database Azure SQL Managed Instance

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.

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 (11.x) y en versiones posteriores, los parámetros con valores de tabla también se almacenan en caché para las consultas parametrizadas.

Permisos

Para crear una instancia de un tipo de tabla definido por el usuario o llamar a un procedimiento almacenado con un parámetro con valores de tabla, el usuario debe tener los permisos EXECUTE y REFERENCES en el tipo o en el esquema o la base de datos que contiene el tipo.

Limitaciones

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 utilizar 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 el procedimiento almacenado o la cadena INSERT EXEC.

Parámetros con valores de tabla 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. 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.

Los parámetros con valores de tabla que se vuelven a utilizar se benefician del almacenamiento en caché de tablas temporales. Este almacenamiento en memoria caché de tablas proporciona una escalabilidad mejor que en el caso de operaciones BULK INSERT equivalentes. 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. 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.

Ejemplos

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 en la base de datos de muestra de AdventureWorks.

/* 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 AdventureWorks2022.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 AdventureWorks2022.Person.StateProvince;
  
/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;

El resiltset esperado es:

(181 rows affected)

Contenido relacionado