Tabela (Transact-SQL)

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada SQL do Azurebanco de dados SQL no Microsoft Fabric

tabela é um tipo de dados especial usado para armazenar um conjunto de resultados para processamento posterior. de tabela é usado principalmente para armazenar temporariamente um conjunto de linhas que são retornadas como o conjunto de resultados da função com valor de tabela. Funções e variáveis podem ser declaradas como sendo do tipo tabela. tabela variáveis podem ser usadas em funções, procedimentos armazenados e lotes. Para declarar variáveis do tipo tabela, use DECLARE @local_variable.

Transact-SQL convenções de sintaxe

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 que é 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 que é composto por 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 for definida como um tipo definido pelo usuário CLR (Common Language Runtime), a coluna herdará o agrupamento do tipo definido pelo usuário.

Comentários

tabela Variáveis de referência 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, tabela variáveis devem ser referenciadas usando um alias, conforme mostrado no exemplo a seguir:

SELECT EmployeeID,
    DepartmentID
FROM @MyTableVar m
INNER JOIN Employee
    ON m.EmployeeID = Employee.EmployeeID
    AND m.DepartmentID = Employee.DepartmentID;

tabela variáveis fornecem os seguintes benefícios em relação às tabelas temporárias 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 tabela variável se comporta como uma variável local. Tem um âmbito bem definido. Essa variável pode ser usada na função, procedimento armazenado ou lote em que é declarada.

  Dentro de seu escopo, uma tabela variável pode ser usada como uma tabela regular. Ele pode ser aplicado em qualquer lugar onde uma tabela ou expressão de tabela é usada nas instruções SELECT, INSERT, UPDATE e DELETE. No entanto, tabela não pode ser usada na seguinte instrução:

SELECT select_list INTO table_variable;

tabela variáveis são limpas automaticamente no final da função, do procedimento armazenado ou do lote em que estão definidas.

• tabela variáveis usadas em procedimentos armazenados causam menos recompilações de procedimentos armazenados do que quando tabelas temporárias são usadas quando não há opções baseadas em custo que afetem o desempenho.

  As variáveis de tabela são completamente isoladas no lote que as cria, portanto, nenhuma de reresolução precisa ocorrer quando uma instrução CREATE ou ALTER ocorre, 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 essa etapa 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 tabela variáveis duram apenas durante uma atualização na tabela variável. Como tal, tabela variáveis exigem menos recursos de bloqueio e registro.

Limitações e restrições

tabela variáveis não tem estatísticas de distribuição. Eles não acionam recompilações. Em muitos casos, o otimizador cria um plano de consulta com base na suposição de que a variável de tabela não tem linhas. Por esse motivo, você deve ser cauteloso ao usar uma variável de tabela se você espera um número maior de linhas (maior que 100). As tabelas temporárias podem ser uma solução melhor neste caso. Para consultas que unem a variável table com outras tabelas, use a dica RECOMPILE, que faz com que o otimizador use a cardinalidade correta para a variável table.

tabela variáveis não têm suporte no modelo de raciocínio baseado em custo do otimizador do SQL Server. Como tal, eles não devem ser usados quando escolhas baseadas em custos são necessárias para alcançar um plano de consulta eficiente. As tabelas temporárias são preferidas quando são necessárias opções baseadas no custo. Esse plano geralmente inclui consultas com junções, decisões de paralelismo e opções de seleção de índice.

As consultas que modificam tabela variáveis não geram planos de execução de consultas paralelas. O desempenho pode ser afetado quando grandes variáveis de tabela ou tabela variáveis em consultas complexas são modificadas. Em vez disso, considere o uso de tabelas temporárias em situações em que tabela variáveis são modificadas. Para obter mais informações, consulte CREATE TABLE (Transact-SQL). As consultas que leem tabela variáveis sem modificá-las ainda podem ser paralelizadas.

Importante

O nível de compatibilidade do banco de dados 150 melhora o desempenho das variáveis de tabela com a introdução de de compilação diferida da variável de tabela. Para obter mais informações, consulte Compilação adiada da variável Tabela.

Os índices não podem ser criados explicitamente em tabela variáveis e nenhuma estatística é mantida em tabela variáveis. A partir do SQL Server 2014 (12.x), foi introduzida uma nova sintaxe que permite criar determinados tipos de índice em linha com a definição da tabela. Usando essa nova sintaxe, você pode criar índices em tabela variáveis como parte da definição da tabela. Em alguns casos, o desempenho pode melhorar usando tabelas temporárias, que fornecem suporte total ao índice e estatísticas. Para obter mais informações sobre tabelas temporárias e criação de índice embutido, consulte CREATE TABLE (Transact-SQL).

Restrições CHECK, valores DEFAULT e colunas computadas na tabela declaração de tipo não podem chamar funções definidas pelo usuário. Não há suporte para a operação de atribuição entre tabela variáveis. Como variáveis de tabela têm escopo limitado e não fazem parte do banco de dados persistente, as reversões de transação não as afetam. As variáveis de tabela não podem ser alteradas após a criação.

As variáveis de tabelas não podem ser usadas como destino da cláusula INTO em uma instrução SELECT ... INTO.

Não é possível 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 tiver sido criada fora da instrução EXEC ou do procedimento armazenado sp_executesql. Como as variáveis de tabela podem ser referenciadas apenas em seu 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 cabe na memória, ela precisa ter um lugar no disco para armazenar dados. As variáveis de tabela são criadas no banco de dados tempdb semelhante às 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 enquanto estiverem na memória (cache de dados).

Variáveis de tabela vs tabelas temporárias

A escolha entre variáveis de tabela e tabelas temporárias depende destes fatores:

• O número de linhas inseridas na tabela.
• O número de recompilações das quais a consulta é salva.
• O tipo de consultas e sua dependência de índices e estatísticas de 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 as tabelas temporárias para uma consulta específica ou procedimento armazenado.

Exemplos

Um. Declarar uma variável do tipo tabela

O exemplo a seguir cria uma tabela variável que armazena os valores especificados na cláusula OUTPUT da instrução UPDATE. Seguem-se duas instruções SELECT, que devolvem os valores em @MyTableVar e os resultados da operação de atualização na tabela Employee. Os resultados na coluna INSERTED.ModifiedDate diferem dos valores na coluna ModifiedDate na tabela Employee. Essa diferença ocorre porque o gatilho AFTER UPDATE, que atualiza o valor de ModifiedDate para a data atual, é definido na tabela Employee. No entanto, as colunas retornadas de OUTPUT refletem os dados antes que os gatilhos sejam acionados. Para obter mais informações, consulte 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 embutido

O exemplo a seguir retorna uma função com valor de tabela embutido. Ele retorna três colunas ProductID, Namee o agregado de totais do ano até a data por loja como YTD Total para cada produto vendido à 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);

Ver também

• COLLATE (Transact-SQL)
• FUNÇÃO CREATE (Transact-SQL)
• User-Defined Funções
• CRIAR TABELA (Transact-SQL)
• DECLARE @local_variable (Transact-SQL)
• Usar parâmetros de Table-Valued (Mecanismo de Banco de Dados)
• Dicas de consulta (Transact-SQL)