Partilhar via

  • Artigo

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

Os parâmetros com valor de tabela são um novo tipo de parâmetro no SQL Server 2008. 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.

ObservaçãoObservação

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.

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

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.

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.

Escopo

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.

Segurança

As permissões para parâmetros com valor de tabela seguem o modelo de segurança de objeto do SQL Server, usando as seguintes palavras-chave do Transact-SQL: CREATE, GRANT, DENY, ALTER, CONTROL, TAKE OWNERSHIP, REFERENCES, EXECUTE, VIEW DEFINITION e REVOKE.

Para obter informações associadas aos parâmetros com valor de tabela, você pode consultar as seguintes exibições do catálogo: sys.parameters (Transact-SQL), sys.types (Transact-SQL) e sys.table_types (Transact-SQL).

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.

Fonte de dados

Lógica do servidor

Número de linhas

Melhor tecnologia

Arquivo de dados formatado no servidor

Inserção direta

< 1000

BULK INSERT

Arquivo de dados formatado no servidor

Inserção direta

> 1000

BULK INSERT

Arquivo de dados formatado no servidor

Complexo

< 1000

Parâmetros com valor de tabela

Arquivo de dados formatado no servidor

Complexo

> 1000

BULK INSERT

Processo de cliente remoto

Inserção direta

< 1000

Parâmetros com valor de tabela

Processo de cliente remoto

Inserção direta

> 1000

BULK INSERT

Processo de cliente remoto

Complexo

< 1000

Parâmetros com valor de tabela

Processo de cliente remoto

Complexo

> 1000

Parâmetros com valor de tabela

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