Parâmetros com valor de tabela (Mecanismo de Banco de Dados)

Criando e usando parâmetros com valor de tabela no Transact-SQL

Os parâmetros com valor de tabela têm dois componentes principais: um tipo SQL Server e um parâmetro que referencia esse tipo. Para criar e usar parâmetros com valor de tabela, siga estas etapas:

1. Crie um tipo de tabela e defina a estrutura da tabela.

   Para obter informações sobre como criar um tipo de SQL Server, consulte Tipos de tabela definidos pelo usuário. Para obter mais informações sobre como definir uma estrutura de tabela, consulte CREATE TABLE (Transact-SQL).

2. Declare uma rotina que tenha um parâmetro do tipo de tabela. Para obter mais informações sobre rotinas SQL Server, consulte CREATE PROCEDURE (Transact-SQL) e CREATE FUNCTION (Transact-SQL).

3. Declare uma variável do tipo de tabela e referencie o tipo de tabela. Para obter informações sobre como declarar variáveis, consulte DECLARE @local_variable (Transact-SQL).

4. Preencha a variável de tabela usando uma instrução INSERT. Para obter mais informações sobre como inserir dados, consulte Adicionando linhas usando INSERT e SELECT.

5. Depois que a variável de tabela é criada e preenchida, você poderá passar a variável para uma rotina.

   Quando a rotina estiver fora do escopo, o parâmetro com valor de tabela não estará mais disponível. A definição do tipo permanecerá até ser descartada.

Para usar um parâmetro com valor de tabela no SQL Server Native Client, consulte Parâmetros com valor de tabela (SQL Server Native Client).

Para usar um parâmetro com valor de tabela no ADO.NET, consulte a documentação ADO.NET.

Benefícios

Restrições

Escopo

Segurança

Exibições do catálogo

Parâmetros com valor de tabela versus operações BULK INSERT

O uso de parâmetros com valor de tabela é comparável a outros modos de usar variáveis com base em conjunto; no entanto, o uso de parâmetros com valor de tabela normalmente pode ser mais rápido em grandes conjuntos de dados. Comparado a operações em massa que têm um custo maior de inicialização, os parâmetros com valor de tabela têm bom desempenho para inserção de menos de 1000 linhas.

Os parâmetros com valor de tabela que são reutilizados beneficiam-se de cache de tabela temporária. Esse cache de tabela habilita uma escalabilidade melhor do que operações BULK INSERT equivalentes. Ao usar pequenas operações de inserção de linha, pode haver um pequeno ganho de benefício de desempenho se forem usadas listas de parâmetros ou instruções processadas em lotes em vez de operações BULK INSERT ou parâmetros com valor de tabela. Porém, esses métodos são menos convenientes ao programa e o desempenho diminui rapidamente à medida que as linhas aumentam.

Os parâmetros com valor de tabela têm desempenho igualmente bom ou melhor do que uma implementação de matriz de parâmetros equivalente.

As tabelas a seguir mostram a tecnologia a ser usada com base na velocidade das operações de inserção.

Exemplos

O exemplo a seguir usa o Transact-SQL e mostra como criar um tipo de parâmetro com valor de tabela, declarar uma variável para referenciá-lo, preencher a lista de parâmetros e passar os valores para um procedimento armazenado.

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

Consulte também

Referência