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

  • Artigo

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure

Os parâmetros com valor de tabela 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 a criação de 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.

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.
  • São armazenados em cache como uma tabela temporária quando usado em um procedimento armazenado. A partir do SQL Server 2012 (11.x) e versões posteriores, os parâmetros com valor de tabela também são armazenados em cache para consultas parametrizadas.

Permissões

Para criar uma instância de um tipo de tabela definido pelo usuário ou chamar um procedimento armazenado com um parâmetro com valor de tabela, o usuário deve ter permissões EXECUTE e REFERENCES no tipo ou no esquema ou banco de dados que contém o tipo.

Limitaçõ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.

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 1.000 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. Pequenas operações de inserção de linha podem fornecer um pequeno benefício de desempenho usando listas de parâmetros ou instruções em lote, 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.

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 no banco de dados da amostra 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;

O conjunto de resultados esperado é:

(181 rows affected)

Conteúdo relacionado