tabela (Transact-SQL)
Aplica-se a: 
SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure
tabela é um tipo de dados especial que pode ser usado para armazenar um conjunto de resultados para processamento posterior. table é usada 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. Variáveis table podem ser usadas em funções, procedimentos armazenados e lotes. Para declarar variáveis do tipo table, use DECLARE @local_variable.
Convenções de sintaxe de 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, confira CREATE TABLE (Transact-SQL), CREATE FUNCTION (Transact-SQL) e DECLARE @local_variable (Transact-SQL).
collation_definition
A ordenação 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 uma ordenação do Microsoft SQL Server. Se collation_definition não for especificado, a coluna herdará a ordenação 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á a ordenação do tipo definido pelo usuário.
Comentários
table As variáveis 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 mostra o exemplo a seguir:
SELECT EmployeeID,
DepartmentID
FROM @MyTableVar m
INNER JOIN Employee
ON m.EmployeeID = Employee.EmployeeID
AND m.DepartmentID = Employee.DepartmentID;
Variáveis Tabela fornecem os benefícios seguintes sobre tabelas temporárias 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. Essa variável pode ser usada na função, no procedimento armazenado ou no lote em que ela é 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 variáveis de tabela são completamente isoladas no lote que as cria, portanto, nenhuma nova resolução ocorre quando ocorre uma instrução CREATE ou ALTER, o que pode ocorrer com uma tabela temporária. As tabelas temporárias precisam dessa nova resolução para que a tabela possa ser referenciada a partir de um procedimento armazenado aninhado. As variáveis de tabela evitam isso completamente para que os procedimentos armazenados possam usar o plano que já está compilado, economizando recursos para processar o procedimento armazenado.
As transações que envolvem variáveis table só existem durante uma atualização na variável table. Assim, variáveis table requerem menos recursos de log e bloqueio.
Limitações e restrições
As variáveis table não têm estatísticas de distribuição. Elas não dispararão recompilações. Em muitos casos, o otimizador cria um plano de consulta supondo que a variável de tabela não tenha 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. Para consultas que unem a variável de tabela com outras tabelas, use a dica RECOMPILE, que leva 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 forem 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, esse plano 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 grandes ou variáveis table em consultas complexas forem modificadas. Considere o uso de tabelas temporárias em situações em que as variáveis table são modificadas. Para obter mais informações, veja CREATE TABLE (Transact-SQL). As consultas que leem variáveis table sem modificá-las ainda podem ser colocadas em paralelo.
Importante
O nível de compatibilidade do banco de dados 150 melhora o desempenho das variáveis de tabela com a introdução da compilação adiada da variável de tabela. Para saber mais, veja Compilação adiada de variável da tabela.
Índices não podem ser criados explicitamente em variáveis table e nenhuma estatística é mantida em variáveis table. A partir do SQL Server 2014 (12.x), uma nova sintaxe foi introduzida, a qual permite que você crie determinados tipos de índice em linha com a definição da tabela. Usando essa nova sintaxe, você pode criar índices em variáveis de tabela como parte da definição de tabela. Em alguns casos, o desempenho pode melhorar com o uso de tabelas temporárias em vez disso, que fornecem suporte de índice completo e estatísticas. Para obter mais informações sobre tabelas temporárias e criação de índice embutido, confira CREATE TABLE (Transact-SQL).
Restrições CHECK, valores DEFAULT e colunas computadas na declaração de tipo table não podem chamar funções definidas pelo usuário. Não é dado 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 table não podem ser alteradas após a criação.
As variáveis de tabelas não podem ser usadas como destino da INTO cláusula em uma SELECT ... INTO instrução.
Você não pode usar a instrução EXEC ou o procedimento armazenado sp_executesql para executar uma consulta dinâmica do SQL Server que se refere a uma variável de tabela, se a variável de tabela foi criada fora da instrução EXEC ou do procedimento armazenado sp_executesql. Como as variáveis de tabela só podem ser referenciadas no escopo local, uma instrução EXEC e um procedimento armazenado sp_executesql estariam fora do escopo da variável de tabela. No entanto, você pode criar a variável de tabela e executar todo o processamento dentro da instrução EXEC ou do procedimento armazenado sp_executesql porque, em seguida, o escopo local das variáveis de tabela está na instrução EXEC ou no procedimento armazenado sp_executesql.
Uma variável de tabela não é uma estrutura somente de memória. Como uma variável de tabela pode conter mais dados do que pode caber na memória, ela precisa ter um local no disco para armazenar dados. As variáveis de tabela são criadas no banco de dados tempdb semelhante a tabelas temporárias. Se a memória estiver disponível, as variáveis de tabela e as tabelas temporárias serão criadas e processadas na memória (cache de dados).
Variáveis de tabela versus tabelas temporárias
Escolher entre variáveis de tabela e tabelas temporárias depende desses fatores:
- O número de linhas inseridas na tabela.
- O número de recompilações da qual a consulta é salva.
- O tipo de consultas e sua dependência em índices e estatísticas para desempenho.
Em algumas situações, é útil dividir um procedimento armazenado com tabelas temporárias em procedimentos armazenados menores para que a recompilação ocorra em unidades menores.
Em geral, você usa variáveis de tabela sempre que possível, exceto quando há um volume significativo de dados e há uso repetido da tabela. Nesse caso, você pode criar índices na tabela temporária para aumentar o desempenho da consulta. No entanto, cada cenário pode ser diferente. A Microsoft recomenda que você teste se as variáveis de tabela são mais úteis do que tabelas temporárias para uma consulta ou procedimento armazenado específico.
Exemplos
a. Declarar 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. Os resultados na coluna INSERTED.ModifiedDate são diferentes dos valores da coluna ModifiedDate na tabela Employee. Essa diferença é devido ao gatilho AFTER UPDATE, que atualiza o valor de ModifiedDate com a data atual, e 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, confira Cláusula OUTPUT (Transact-SQL).
USE AdventureWorks2022;
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. Criar 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 para a loja.
USE AdventureWorks2022;
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
INNER JOIN Sales.SalesOrderDetail AS SD
ON SD.ProductID = P.ProductID
INNER JOIN Sales.SalesOrderHeader AS SH
ON SH.SalesOrderID = SD.SalesOrderID
INNER 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);