Parámetros de valores de tabla (motor de base de datos)

Crear y usar parámetros con valores de tabla en Transact-SQL

Los parámetros con valores de tabla tienen dos componentes principales: un tipo de SQL Server y un parámetro que hace referencia a ese tipo. Para crear y usar parámetros con valores de tabla, siga estos pasos:

1. Cree un tipo de tabla y defina la estructura de la tabla.

   Para obtener más información sobre cómo crear un tipo de SQL Server, vea Tipos definidos por el usuario. Para obtener información sobre la forma de definir una estructura de tabla, vea CREATE TABLE (Transact-SQL).

2. Declare una rutina que tenga un parámetro del tipo de tabla. Para obtener más información sobre rutinas de SQL Server, vea CREATE PROCEDURE (Transact-SQL) y CREATE FUNCTION (Transact-SQL).

3. Declare una variable del tipo de tabla y haga referencia al tipo de tabla. Para obtener información sobre cómo declarar variables, vea DECLARE @local_variable (Transact-SQL).

4. Rellene la variable de tabla utilizando una instrucción INSERT. Para obtener más información sobre cómo se insertan datos, vea Agregar filas mediante INSERT y SELECT.

5. Una vez creada y rellena la variable de tabla, puede pasar la variable a una rutina.

   Cuando la rutina esté fuera del ámbito, el parámetro con valores de tabla dejará de estar disponible. La definición de tipo permanecerá hasta que se quite.

Para usar un parámetro con valores de tabla en SQL Server Native Client, vea Parámetros con valores de tabla (SQL Server Native Client).

Para usar un parámetro con valores tabla en ADO.NET, vea la documentación de ADO.NET.

Ventajas

Restricciones

Ámbito

Seguridad

Vistas de catálogo

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

La tabla siguiente muestra qué tecnología se debe utilizar basándose en la velocidad de las operaciones de inserción.

Ejemplos

En el ejemplo siguiente se utiliza Transact-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.

USE AdventureWorks2008R2;
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 usp_InsertProductionLocation
    @TVP LocationTableType READONLY
    AS 
    SET NOCOUNT ON
    INSERT INTO [AdventureWorks2008R2].[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 
    [AdventureWorks2008R2].[Person].[StateProvince];

/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;
GO

Vea también

Referencia