Usar parâmetros com valor de tabela (Mecanismo de Banco de Dados)

Artigo
06/25/2013

Eles são declarados usando tipos de tabela definidos pelo usuário. Você pode usar parâmetros com valor de tabela para enviar várias linhas de dados para uma rotina ou instrução Transact-SQL, como um procedimento armazenado ou função, sem criar uma tabela temporária ou muitos parâmetros.

Os parâmetros com valor de tabela são como matrizes de parâmetro em OLE DB e ODBC, mas oferecem mais flexibilidade e integração mais próxima ao Transact-SQL. Eles também têm o benefício de poder participar de operações com base em conjunto.

O Transact-SQL passa parâmetros com valor de tabela para rotinas por referência, para evitar fazer uma cópia dos dados de entrada. Você pode criar e executar rotinas Transact-SQL com parâmetros com valor de tabela e chamá-las do código Transact-SQL, de clientes nativos e gerenciados em qualquer linguagem gerenciada.

Neste tópico:

Benefícios

Restrições

Parâmetros com valor de tabela vs. Operações BULK INSERT

Exemplo

Benefícios

Um parâmetro com valor de tabela é delimitado ao procedimento armazenado, à função ou ao texto Transact-SQL dinâmico, exatamente como outros parâmetros. Do mesmo modo, uma variável de tipo de tabela tem escopo como qualquer outra variável local criada com uma instrução DECLARE. Você pode declarar variáveis com valor de tabela em instruções Transact-SQL dinâmicas e passar essas variáveis como parâmetros com valor de tabela para funções e procedimentos armazenados.

Os parâmetros com valor de tabela oferecem mais flexibilidade e, em alguns casos, melhor desempenho do que tabelas temporárias ou outras formas de passar uma lista de parâmetros. Eles oferecem os seguintes benefícios:

Não adquirem bloqueios para a população inicial de dados de um cliente.
Fornecem um modelo de programação simples.
Permitem que você inclua lógica de negócios complexa em uma única rotina.
Reduzem viagens de ida e volta ao servidor.
Podem ter uma estrutura de tabela de cardinalidade diferente.
Possuem rigidez de tipo.
Permitem que o cliente especifique a ordem de classificação e as chaves exclusivas.

Ícone de seta usado com o link Voltar ao Início [Top]

Restrições

Os parâmetros com valor de tabela têm as seguintes restrições:

O SQL Server não mantém estatísticas em colunas de parâmetros com valor de tabela.
Os parâmetros com valor de tabela devem ser passados como parâmetros de entrada READONLY para rotinas Transact-SQL. Não é possível executar operações DML como UPDATE, DELETE ou INSERT em um parâmetro com valor de tabela no corpo de uma rotina.
Você não pode usar um parâmetro com valor de tabela como destino de uma instrução SELECT INTO ou INSERT EXEC. Um parâmetro com valor de tabela pode estar na cláusula FROM de SELECT INTO ou na cadeia de caracteres ou procedimento armazenado INSERT EXEC.

Ícone de seta usado com o link Voltar ao Início [Top]

Parâmetros com valor de tabela vs.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.

Ícone de seta usado com o link Voltar ao Início [Top]

Exemplo

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 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

Ícone de seta usado com o link Voltar ao Início [Top]