Compartilhar via

tabela (Transact-SQL)

  • Artigo

É um tipo de dados especial que pode ser usado para armazenar um conjunto de resultados para processamento posterior. table é utilizada principalmente para o armazenamento temporário de um conjunto de linhas retornadas como o conjunto de resultados de uma função com valor de tabela. Funções e variáveis podem ser declaradas como do tipo table. table pode ser usada em funções, procedimentos armazenados e lotes. Para declarar variáveis do tipo table, use DECLARE @local\_variable.

Aplica-se a: SQL Server (SQL Server 2008 até a versão atual), Banco de dados SQL do Windows Azure (versão inicial até a versão atual).

Ícone de vínculo de tópico Convenções da sintaxe Transact-SQL

Sintaxe

table_type_definition ::= 
    TABLE ( { <column_definition> | <table_constraint> } [ ,...n ] ) 

<column_definition> ::= 
    column_name scalar_data_type 
    [ COLLATE <collation_definition> ] 
    [ [ DEFAULT constant_expression ] | IDENTITY [ ( seed , increment ) ] ] 
    [ ROWGUIDCOL ] 
    [ column_constraint ] [ ...n ] 

<column_constraint> ::= 
    { [ NULL | NOT NULL ] 
    | [ PRIMARY KEY | UNIQUE ] 
    | CHECK ( logical_expression ) 
    } 

<table_constraint> ::= 
     { { PRIMARY KEY | UNIQUE } ( column_name [ ,...n ] )
     | CHECK ( logical_expression ) 
     }

Argumentos

  • table_type_definition
    É o mesmo subconjunto de informações usado para definir uma tabela em CREATE TABLE. A declaração de tabela inclui definições de coluna, nomes, tipos de dados e restrições. Os únicos tipos de restrição permitidos são PRIMARY KEY, UNIQUE KEY e NULL.

    Para obter mais informações sobre a sintaxe, consulte CREATE TABLE (SQL Server), CREATE FUNCTION (Transact-SQL) e DECLARE @local\_variable (Transact-SQL).

  • collation_definition
    É o agrupamento da coluna composta de uma localidade do Microsoft Windows e um estilo de comparação, uma localidade do Windows e a notação binária ou um agrupamento do Microsoft SQL Server. Se collation_definition não for especificado, a coluna herdará o agrupamento do banco de dados atual. Ou se a coluna estiver definida como um tipo CLR (Common Language Runtime) definido pelo usuário, a coluna herdará o agrupamento do tipo definido pelo usuário.

Comentários gerais

As variáveis table podem ser referenciadas por nome na cláusula FROM de um lote, conforme mostrado no seguinte exemplo:

SELECT Employee_ID, Department_ID FROM @MyTableVar;

Fora de uma cláusula FROM, as variáveis table devem ser referenciadas usando um alias, conforme mostrado no exemplo a seguir:

SELECT EmployeeID, DepartmentID 
FROM @MyTableVar m
JOIN Employee on (m.EmployeeID =Employee.EmployeeID AND
   m.DepartmentID = Employee.DepartmentID);

Variáveis table fornecem os benefícios seguintes para consultas em pequena escala que têm planos de consulta que não são alterados e quando há preocupações de recompilação:

  • Uma variável table se comporta como uma variável local. Ela tem um escopo bem definido. Ela é a função, o procedimento armazenado ou o lote em que está declarada.

    Dentro de seu escopo, uma variável table pode ser usada como uma tabela comum. Pode ser aplicada em qualquer lugar em que uma tabela ou expressão de tabela for usada em instruções SELECT, INSERT, UPDATE e DELETE. Porém, table não pode ser usada na seguinte instrução:

    SELECT select_list INTO table_variable;

    As variáveis table são automaticamente limpas ao término da função, do procedimento armazenado ou do lote em que estão definidas.

  • As variáveis table usadas em procedimentos armazenados provocam menos recompilações dos procedimentos armazenados do que quando tabelas temporárias são usadas, quando não há opções baseadas no custo que afetem o desempenho.

  • As transações que envolvem variáveis table só existem durante uma atualização na variável table. Portanto, variáveis table requerem menos recursos de log e bloqueio.

Limitações e restrições

Variáveis Table não têm estatísticas de distribuição; elas não dispararão recompilações. Portanto, em muitos casos, o otimizador criará um plano de consulta supondo que a variável de tabela não tem linhas Por esse motivo, tenha cuidado ao usar uma variável de tabela se espera um grande número de linhas (cima de 100). Nesse caso, talvez seja melhor usar tabelas temporárias. Outra opção, para consultas que unem a variável de tabela com outras tabelas, é usar a dica RECOMPILE, que levará o otimizador a usar a cardinalidade correta para a variável de tabela.

Não há suporte para variáveis table no modelo de raciocínio baseado no custo do otimizador do SQL Server. Portanto, elas não devem ser usadas quando opções baseadas no custo são necessárias para obter um plano de consulta eficiente. Tabelas temporárias são preferíveis quando opções baseadas no custo são necessárias. Em geral, isso inclui consultas com junções, decisões de paralelismo e opções de seleção de índice.

As consultas que modificam variáveis table não geram planos de execução de consulta paralelos. O desempenho pode ser afetado quando variáveis table muito grandes ou variáveis table em consultas complexas, forem modificadas. Nessas situações, considere o uso de tabelas temporárias em seu lugar. Para obter mais informações, consulte CREATE TABLE (SQL Server). As consultas que leem variáveis table sem modificá-las ainda podem ser colocadas em paralelo.

Não é possível criar índices explicitamente sobre variáveis table e nenhuma estatística é mantida sobre variáveis table. Em alguns casos, o desempenho pode melhorar com o uso de tabelas temporárias em seu lugar, as quais oferecem suporte a índices e estatísticas. Para obter mais informações sobre tabelas temporárias, consulte CREATE TABLE (SQL Server).

Restrições CHECK, valores DEFAULT e colunas computadas na declaração do tipo table não podem chamar funções definidas pelo usuário.

Não é oferecido suporte à operação de atribuição entre variáveis table.

Como as variáveis table têm escopo limitado e não fazem parte do banco de dados persistente, elas não são afetadas por reversões de transações.

As variáveis de tabela não podem ser alteradas após a criação.

Exemplos

A.Declarando uma variável de tabela de tipos

O exemplo a seguir cria uma variável table que armazena os valores especificados na cláusula OUTPUT da instrução UPDATE. Seguem duas instruções SELECT que retornam os valores em @MyTableVar e os resultados da operação de atualização na tabela Employee. Observe que os resultados na coluna INSERTED.ModifiedDate são diferentes dos valores da coluna ModifiedDate na tabela Employee . Isso porque o gatilho AFTER UPDATE, que atualiza o valor de ModifiedDate com a data atual, está definido na tabela Employee. Porém, as colunas retornadas de OUTPUT refletem os dados antes de os gatilhos serem disparados. Para obter mais informações, consulte cláusula OUTPUT (Transact-SQL).

USE AdventureWorks2012;
GO
DECLARE @MyTableVar table(
    EmpID int NOT NULL,
    OldVacationHours int,
    NewVacationHours int,
    ModifiedDate datetime);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25 
OUTPUT INSERTED.BusinessEntityID,
       DELETED.VacationHours,
       INSERTED.VacationHours,
       INSERTED.ModifiedDate
INTO @MyTableVar;
--Display the result set of the table variable.
SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate
FROM @MyTableVar;
GO
--Display the result set of the table.
--Note that ModifiedDate reflects the value generated by an
--AFTER UPDATE trigger.
SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO

B.Criando uma função com valor de tabela embutida

O exemplo a seguir retorna uma função com valor de tabela embutida. Ela retorna três colunas ProductID, Name e a agregação dos totais acumulados no ano por loja como YTD Total para cada produto vendido na loja.

USE AdventureWorks2012;
GO
IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL
    DROP FUNCTION Sales.ufn_SalesByStore;
GO
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN 
(
    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'
    FROM Production.Product AS P 
    JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
    JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
    JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID
    WHERE C.StoreID = @storeid
    GROUP BY P.ProductID, P.Name
);
GO

Para invocar a função, execute esta consulta.

SELECT * FROM Sales.ufn_SalesByStore (602);

Consulte também

Referência

COLLATE (Transact-SQL)

CREATE FUNCTION (Transact-SQL)

CREATE TABLE (SQL Server)

DECLARE @local\_variable (Transact-SQL)

dicas de consulta (Transact-SQL)

Conceitos

Funções definidas pelo usuário

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