tabela (Transact-SQL)
É um tipo de dados especial que pode ser usado para armazenar um conjunto de resultados para processamento posterior. table é utilizado 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. As funções e as variáveis podem ser declaradas como sendo do tipo table. As variáveis table podem ser usadas em funções, procedimentos armazenados e lotes. Para declarar variáveis do tipo table, use DECLARE @local_variable.
.gif "Ícone de vínculo de tópico")
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 (Transact-SQL), 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.
Práticas recomendadas
Não use variáveis de tabela para armazenar grandes quantidades de dados (mais de 100 linhas). As opções de plano podem não ser ideais ou estáveis quando uma variável de tabela contém uma grande quantidade de dados. Considere regravar essas consultas para usar tabelas temporárias ou usar a dica de consulta USE PLAN para garantir que o otimizador use um plano de consulta existente que funcione bem para o seu cenário.
Comentários gerais
As variáveis table podem ser referenciadas por nome na cláusula FROM de um lote, conforme mostrado no exemplo a seguir:
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);
As variáveis table fornecem os seguintes benefícios para consultas de pequena escala que têm planos de consulta que não mudam e quando as preocupações de recompilação são dominantes:
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 em custo que afetam 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
Não há suporte para variáveis table no modelo de raciocínio baseado em custo do otimizador do SQL Server. Portanto, elas não devem ser usadas quando opções baseadas em custo são necessárias para obter um plano de consulta eficiente. As tabelas temporárias são preferenciais quando opções baseadas em custo são necessárias. Normalmente, 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 (Transact-SQL). 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 (Transact-SQL).
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.
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 da ação do gatilho. Para obter mais informações, consulte cláusula OUTPUT (Transact-SQL).
USE AdventureWorks2008R2;
GO
DECLARE @MyTableVar table(
EmpID int NOT NULL,
OldVacationHours int,
NewVacationHours int,
ModifiedDate datetime);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25,
ModifiedDate = GETDATE()
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.
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 AdventureWorks2008R2;
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