Partilhar via

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

Aplica-se a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceBase de dados SQL no Microsoft Fabric

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 instrução Transact-SQL ou uma rotina, 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âmetros em OLE DB e ODBC, mas oferecem mais flexibilidade e integração mais estreita com o Transact-SQL. Os parâmetros com valor de tabela também têm a vantagem de poder participar em operações baseadas em conjuntos.

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 de Transact-SQL com parâmetros de valor de tabela e chamá-las a partir de código Transact-SQL, clientes geridos e nativos em qualquer linguagem gerida.

Benefits

Um parâmetro com valor de tabela tem como escopo o procedimento armazenado, a função ou o texto de Transact-SQL dinâmico, exatamente como outros parâmetros. Da mesma forma, uma variável do tipo tabela tem escopo como qualquer outra variável local que é criada usando uma instrução DECLARE . Você pode declarar variáveis com valor de tabela dentro de instruções Transact-SQL dinâmicas e passar essas variáveis como parâmetros com valor de tabela para procedimentos e funções armazenados.

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

  • Não adquira bloqueios para a população inicial de dados de um cliente.
  • Forneça um modelo de programação simples.
  • Permite que você inclua lógica de negócios complexa em uma única rotina.
  • Reduza as viagens de ida e volta para o servidor.
  • Pode ter uma estrutura de tabela com diferentes cardinalidades.
  • São fortemente tipados.
  • Permita que o cliente especifique a ordem de classificação e as chaves exclusivas.
  • São armazenados em cache como uma tabela temporária quando usados 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.

Permissions

Para criar uma instância de um tipo de tabela definido pelo usuárioou 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.

Limitations

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

  • O SQL Server não mantém estatísticas sobre colunas de parâmetros com valor de tabela.
  • Os parâmetros com valor de tabela devem ser passados para rotinas Transact-SQL como parâmetros de entrada READONLY. 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.
  • Não é possível 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, na cadeia de caracteres INSERT EXEC ou no procedimento armazenado.

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

O uso de parâmetros com valor de tabela é comparável a outras maneiras de usar variáveis baseadas em conjuntos; no entanto, o uso frequente de parâmetros com valor de tabela pode ser mais rápido para grandes conjuntos de dados. Em comparação com operações em massa que têm um custo de inicialização maior do que os parâmetros com valor de tabela, os parâmetros com valor de tabela têm um bom desempenho para inserir menos de 1.000 linhas.

Os parâmetros com valor de tabela que são reutilizados se beneficiam do cache temporário de tabelas. Esse cache de tabela permite melhor escalabilidade do que operações equivalentes de inserção em massa. 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. No entanto, esses métodos são menos convenientes de programar e o desempenho diminui rapidamente à medida que as linhas aumentam.

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

Examples

O exemplo a seguir usa Transact-SQL e mostra como criar um tipo de parâmetro com valor de tabela, declarar uma variável para fazer referência a ela, preencher a lista de parâmetros e passar os valores para um procedimento armazenado no banco de dados de AdventureWorks de exemplo.

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

  • Last updated on