Usar parâmetros com valor de tabela (Mecanismo de Banco de Dados)Use Table-Valued Parameters (Database Engine)

ESTE TÓPICO APLICA-SE A: simSQL Server (a partir de 2016)simAzure SQL DatabasesimAzure SQL Data Warehouse simParallel Data Warehouse THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Os parâmetros com valor de tabela são declarados usando tipos de tabela definidos pelo usuário.Table-valued parameters are declared by using user-defined table types. Você pode usar parâmetros com valor de tabela para enviar várias linhas de dados para uma rotina ou instrução Transact-SQLTransact-SQL , como um procedimento armazenado ou função, sem criar uma tabela temporária ou muitos parâmetros.You can use table-valued parameters to send multiple rows of data to a Transact-SQLTransact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters.

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-SQLTransact-SQL.Table-valued parameters are like parameter arrays in OLE DB and ODBC, but offer more flexibility and closer integration with Transact-SQLTransact-SQL. Eles também têm o benefício de poder participar de operações com base em conjunto.Table-valued parameters also have the benefit of being able to participate in set-based operations.

Transact-SQLTransact-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. passes table-valued parameters to routines by reference to avoid making a copy of the input data. Você pode criar e executar rotinas Transact-SQLTransact-SQL com parâmetros com valor de tabela e chamá-las do código Transact-SQLTransact-SQL , de clientes nativos e gerenciados em qualquer linguagem gerenciada.You can create and execute Transact-SQLTransact-SQL routines with table-valued parameters, and call them from Transact-SQLTransact-SQL code, managed and native clients in any managed language.

Neste tópico:In This Topic:

BenefíciosBenefits

RestriçõesRestrictions

Parâmetros com valor de tabela vs. Operações BULK INSERTTable-Valued Parameters vs. BULK INSERT Operations

ExemploExample

Benefícios Benefits

Um parâmetro com valor de tabela é delimitado ao procedimento armazenado, à função ou ao texto Transact-SQLTransact-SQL dinâmico, exatamente como outros parâmetros.A table-valued parameter is scoped to the stored procedure, function, or dynamic Transact-SQLTransact-SQL text, exactly like other parameters. Do mesmo modo, uma variável de tipo de tabela tem escopo como qualquer outra variável local criada com uma instrução DECLARE.Similarly, a variable of table type has scope like any other local variable that is created by using a DECLARE statement. Você pode declarar variáveis com valor de tabela em instruções Transact-SQLTransact-SQL dinâmicas e passar essas variáveis como parâmetros com valor de tabela para funções e procedimentos armazenados.You can declare table-valued variables within dynamic Transact-SQLTransact-SQL statements and pass these variables as table-valued parameters to stored procedures and functions.

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.Table-valued parameters offer more flexibility and in some cases better performance than temporary tables or other ways to pass a list of parameters. Eles oferecem os seguintes benefícios:Table-valued parameters offer the following benefits:

  • Não adquirem bloqueios para a população inicial de dados de um cliente.Do not acquire locks for the initial population of data from a client.

  • Fornecem um modelo de programação simples.Provide a simple programming model.

  • Permitem que você inclua lógica de negócios complexa em uma única rotina.Enable you to include complex business logic in a single routine.

  • Reduzem viagens de ida e volta ao servidor.Reduce round trips to the server.

  • Podem ter uma estrutura de tabela de cardinalidade diferente.Can have a table structure of different cardinality.

  • Possuem rigidez de tipo.Are strongly typed.

  • Permitem que o cliente especifique a ordem de classificação e as chaves exclusivas.Enable the client to specify sort order and unique keys.

  • São armazenados em cache como uma tabela temporária quando usado em um procedimento armazenado.Are cached like a temp table when used in a stored procedure. A partir do SQL Server 2012SQL Server 2012, os parâmetros com valor de tabela também são armazenados em cache para consultas parametrizadas.Starting with SQL Server 2012SQL Server 2012, table-valued parameters are also cached for parameterized queries.

Restrições Restrictions

Os parâmetros com valor de tabela têm as seguintes restrições:Table-valued parameters have the following restrictions:

  • SQL ServerSQL Server não mantém estatísticas em colunas de parâmetros com valor de tabela. does not maintain statistics on columns of table-valued parameters.

  • Os parâmetros com valor de tabela devem ser passados como parâmetros de entrada READONLY para rotinas Transact-SQLTransact-SQL .Table-valued parameters must be passed as input READONLY parameters to Transact-SQLTransact-SQL routines. 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.You cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.

  • Você não pode usar um parâmetro com valor de tabela como destino de uma instrução SELECT INTO ou INSERT EXEC.You cannot use a table-valued parameter as target of a SELECT INTO or INSERT EXEC statement. 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.A table-valued parameter can be in the FROM clause of SELECT INTO or in the INSERT EXEC string or stored procedure.

Parâmetros com valor de tabela vs. Operações BULK INSERT Table-Valued Parameters vs. BULK INSERT Operations

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.Using table-valued parameters is comparable to other ways of using set-based variables; however, using table-valued parameters frequently can be faster for large data sets. 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.Compared to bulk operations that have a greater startup cost than table-valued parameters, table-valued parameters perform well for inserting less than 1000 rows.

Os parâmetros com valor de tabela que são reutilizados beneficiam-se de cache de tabela temporária.Table-valued parameters that are reused benefit from temporary table caching. Esse cache de tabela habilita uma escalabilidade melhor do que operações BULK INSERT equivalentes.This table caching enables better scalability than equivalent BULK INSERT operations. 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.By using small row-insert operations a small performance benefit might be gained by using parameter lists or batched statements instead of BULK INSERT operations or table-valued parameters. Porém, esses métodos são menos convenientes ao programa e o desempenho diminui rapidamente à medida que as linhas aumentam.However, these methods are less convenient to program, and performance decreases quickly as rows increase.

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.Table-valued parameters perform equally well or better than an equivalent parameter array implementation.

Exemplo Example

O exemplo a seguir usa o Transact-SQLTransact-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.The following example uses Transact-SQLTransact-SQL and shows you how to create a table-valued parameter type, declare a variable to reference it, fill the parameter list, and then pass the values to a stored procedure.

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  

Consulte tambémSee Also

CREATE TYPE (Transact-SQL) CREATE TYPE (Transact-SQL)
DECLARE @local_variable (Transact-SQL) DECLARE @local_variable (Transact-SQL)
sys.types (Transact-SQL) sys.types (Transact-SQL)
sys.parameters (Transact-SQL) sys.parameters (Transact-SQL)
sys.parameter_type_usages (Transact-SQL) sys.parameter_type_usages (Transact-SQL)
CREATE PROCEDURE (Transact-SQL) CREATE PROCEDURE (Transact-SQL)
CREATE FUNCTION (Transact-SQL) CREATE FUNCTION (Transact-SQL)