tabela (Transact-SQL)table (Transact-SQL)

APLICA-SE A: simSQL Server simBanco de Dados SQL do Azure nãoAzure Synapse Analytics (SQL DW) nãoParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

É um tipo de dados especial que pode ser usado para armazenar um conjunto de resultados para processamento posterior.Is a special data type used to store a result set for processing at a later time. 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.table is primarily used for temporarily storing a set of rows that are returned as the table-valued function result set. Funções e variáveis podem ser declaradas como do tipo table.Functions and variables can be declared to be of type table. Variáveis table podem ser usadas em funções, procedimentos armazenados e lotes.table variables can be used in functions, stored procedures, and batches. Para declarar variáveis do tipo table, use DECLARE @local_variable.To declare variables of type table, use DECLARE @local_variable.

Aplica-se a: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 a SQL ServerSQL Server), Banco de dados SQL do AzureAzure SQL Database.Applies to: SQL ServerSQL Server (SQL Server 2008SQL Server 2008 through SQL ServerSQL Server), Banco de dados SQL do AzureAzure SQL Database.

Ícone de link do tópico Convenções de sintaxe de Transact-SQLTopic link icon Transact-SQL Syntax Conventions

SintaxeSyntax

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 )   
     }   

ArgumentosArguments

table_type_definitiontable_type_definition
É o mesmo subconjunto de informações usado para definir uma tabela em CREATE TABLE.Is the same subset of information that is used to define a table in CREATE TABLE. A declaração de tabela inclui definições de coluna, nomes, tipos de dados e restrições.The table declaration includes column definitions, names, data types, and constraints. Os únicos tipos de restrição permitidos são PRIMARY KEY, UNIQUE KEY e NULL.The only constraint types allowed are PRIMARY KEY, UNIQUE KEY, and NULL.
Para obter mais informações sobre a sintaxe, veja CREATE TABLE (Transact-SQL), CREATE FUNCTION (Transact-SQL) e DECLARE @local_variable (Transact-SQL).For more information about the syntax, see CREATE TABLE (Transact-SQL), CREATE FUNCTION (Transact-SQL), and DECLARE @local_variable (Transact-SQL).

collation_definitioncollation_definition
É a ordenação da coluna composta de uma localidade do MicrosoftMicrosoft Windows e um estilo de comparação, uma localidade do Windows e a notação binária ou uma ordenação do MicrosoftMicrosoft SQL ServerSQL Server.Is the collation of the column that is made up of a MicrosoftMicrosoft Windows locale and a comparison style, a Windows locale, and the binary notation, or a MicrosoftMicrosoft SQL ServerSQL Server collation. Se collation_definition não for especificado, a coluna herdará a ordenação do banco de dados atual.If collation_definition isn't specified, the column inherits the collation of the current database. 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.Or if the column is defined as a common language runtime (CLR) user-defined type, the column inherits the collation of the user-defined type.

RemarksRemarks

table As variáveis podem ser referenciadas por nome na cláusula FROM de um lote, conforme mostrado no seguinte exemplo:table Reference variables by name in a batch's FROM clause, as shown the following example:

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:Outside a FROM clause, table variables must be referenced by using an alias, as shown in the following example:

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:table variables provide the following benefits for small-scale queries that have query plans that don't change and when recompilation concerns are dominant:

  • Uma variável table se comporta como uma variável local.A table variable behaves like a local variable. Ela tem um escopo bem definido.It has a well-defined scope. Essa variável é a função, o procedimento armazenado ou o lote em que está declarada.This variable is the function, stored procedure, or batch that it's declared in.
    Dentro de seu escopo, uma variável table pode ser usada como uma tabela comum.Within its scope, a table variable can be used like a regular table. 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.It may be applied anywhere a table or table expression is used in SELECT, INSERT, UPDATE, and DELETE statements. Porém, table não pode ser usada na seguinte instrução:However, table can't be used in the following statement:
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.table variables are automatically cleaned up at the end of the function, stored procedure, or batch in which they're defined.

  • 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.table variables that are used in stored procedures cause fewer stored procedure recompilations than when temporary tables are used when there are no cost-based choices that affect performance.
  • As transações que envolvem variáveis table só existem durante uma atualização na variável table.Transactions involving table variables last only for the duration of an update on the table variable. Assim, variáveis table requerem menos recursos de log e bloqueio.As such, table variables require less locking and logging resources.

Limitações e restriçõesLimitations and restrictions

As variáveis Table não têm estatísticas de distribuição.Table variables don't have distribution statistics. Elas não dispararão recompilações.They won't trigger recompiles. Em muitos casos, o otimizador criará um plano de consulta supondo que a variável de tabela não tenha linhas.In many cases, the optimizer will build a query plan on the assumption that the table variable has no rows. Por esse motivo, tenha cuidado ao usar uma variável de tabela se espera um grande número de linhas (cima de 100).For this reason, you should be cautious about using a table variable if you expect a larger number of rows (greater than 100). Nesse caso, talvez seja melhor usar tabelas temporárias.Temp tables may be a better solution in this case. Para consultas que unem a variável de tabela com outras tabelas, use a dica RECOMPILE, que levará o otimizador a usar a cardinalidade correta para a variável de tabela.For queries that join the table variable with other tables, use the RECOMPILE hint, which will cause the optimizer to use the correct cardinality for the table variable.

Não há suporte para variáveis table no modelo de raciocínio baseado no custo do otimizador do SQL ServerSQL Server.table variables aren't supported in the SQL ServerSQL Server optimizer's cost-based reasoning model. Portanto, elas não devem ser usadas quando opções baseadas no custo forem necessárias para obter um plano de consulta eficiente.As such, they shouldn't be used when cost-based choices are required to achieve an efficient query plan. Tabelas temporárias são preferíveis quando opções baseadas no custo são necessárias.Temporary tables are preferred when cost-based choices are required. Em geral, esse plano inclui consultas com junções, decisões de paralelismo e opções de seleção de índice.This plan typically includes queries with joins, parallelism decisions, and index selection choices.

As consultas que modificam variáveis table não geram planos de execução de consulta paralelos.Queries that modify table variables don't generate parallel query execution plans. O desempenho pode ser afetado quando variáveis table grandes ou variáveis table em consultas complexas forem modificadas.Performance can be affected when large table variables, or table variables in complex queries, are modified. Considere o uso de tabelas temporárias em situações em que as variáveis table são modificadas.Consider using temporary tables instead in situations where table variables are modified. Para obter mais informações, consulte CREATE TABLE (Transact-SQL).For more information, see CREATE TABLE (Transact-SQL). As consultas que leem variáveis table sem modificá-las ainda podem ser colocadas em paralelo.Queries that read table variables without modifying them can still be parallelized.

Índices não podem ser criados explicitamente em variáveis table e nenhuma estatística é mantida em variáveis table.Indexes can't be created explicitly on table variables, and no statistics are kept on table variables. A partir de SQL Server 2014 (12.x)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.Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x), new syntax was introduced which allows you to create certain index types inline with the table definition. Usando essa nova sintaxe, você pode criar índices em variáveis de tabela como parte da definição de tabela.Using this new syntax, you can create indexes on table variables as part of the table definition. 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.In some cases, performance may improve by using temporary tables instead, which provide full index support and statistics. Para saber mais sobre tabelas temporárias e criação de índice embutido, confira CREATE TABLE (Transact-SQL).For more information about temporary tables and inline index creation, see 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.CHECK constraints, DEFAULT values, and computed columns in the table type declaration can't call user-defined functions.

Não é dado suporte à operação de atribuição entre variáveis table.Assignment operation between table variables isn't supported.

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.Because table variables have limited scope and aren't part of the persistent database, transaction rollbacks don't affect them.

As variáveis table não podem ser alteradas após a criação.Table variables can't be altered after creation.

Compilação adiada de variável da tabelaTable variable deferred compilation

A compilação adiada de variável da tabela melhora a qualidade do plano e o desempenho geral para consultas que fazem referência a variáveis de tabela.Table variable deferred compilation improves plan quality and overall performance for queries referencing table variables. Durante a otimização e a compilação do plano inicial, esse recurso propagará estimativas de cardinalidade com base nas contagens reais de linha de variável de tabela.During optimization and initial plan compilation, this feature will propagate cardinality estimates that are based on actual table variable row counts. Essas informações de contagem de linha precisas serão usadas para otimizar operações de plano de downstream.This exact row count information will then be used for optimizing downstream plan operations.

Observação

A compilação adiada de variável de tabela é uma versão prévia pública do recurso em Banco de dados SQL do AzureAzure SQL Database e SQL Server 2019 (15.x)SQL Server 2019 (15.x).Table variable deferred compilation is a public preview feature in Banco de dados SQL do AzureAzure SQL Database and SQL Server 2019 (15.x)SQL Server 2019 (15.x).

Com a compilação adiada de variável de tabela, a compilação de uma instrução que faz referência a uma variável de tabela é adiada até a primeira execução real da instrução.With table variable deferred compilation, compilation of a statement that references a table variable is deferred until the first actual execution of the statement. Esse comportamento de compilação adiada é idêntico ao das tabelas temporárias.This deferred compilation behavior is identical to the behavior of temporary tables. Essa alteração resulta no uso de cardinalidade real em vez da estimativa original de uma linha.This change results in the use of actual cardinality instead of the original one-row guess.

Para habilitar a versão prévia pública da compilação adiada de variável table, habilite o nível de compatibilidade do banco de dados 150 para o banco de dados ao qual você está conectado ao executar a consulta.To enable the public preview of table variable deferred compilation, enable database compatibility level 150 for the database you're connected to when the query runs.

A compilação adiada de variável table não altera nenhuma outra característica das variáveis de tabela.Table variable deferred compilation doesn't change any other characteristics of table variables. Por exemplo, esse recurso não adiciona as estatísticas de coluna às variáveis table.For example, this feature doesn't add column statistics to table variables.

A compilação adiada de variável table não aumenta a frequência de recompilação.Table variable deferred compilation doesn't increase recompilation frequency. Em vez disso, ela alterna onde ocorre a compilação inicial.Rather, it shifts where the initial compilation occurs. O plano armazenado em cache resultante é gerado com base na contagem da linha de variável table de compilação adiada inicial.The resulting cached plan generates based on the initial deferred compilation table variable row count. O plano armazenado em cache é reutilizado por consultas consecutivas.The cached plan is reused by consecutive queries. Ele é reutilizado até que o plano seja removido ou recompilado.It's reused until the plan is evicted or recompiled.

A contagem de linha de variável de tabela que é usada para a compilação de plano inicial representa um valor típico que pode ser diferente de uma estimativa de contagem de linha fixa.Table variable row count that is used for initial plan compilation represents a typical value might be different from a fixed row count guess. Se ele for diferente, as operações downstream serão beneficiadas.If it's different, downstream operations will benefit. O desempenho poderá não ser melhorado por esse recurso se a contagem de linha da variável table variar consideravelmente entre as execuções.Performance may not be improved by this feature if the table variable row count varies significantly across executions.

Desabilitar a compilação adiada de variável de tabela sem alterar o nível de compatibilidadeDisabling table variable deferred compilation without changing the compatibility level

Desabilitar a compilação adiada de variável table no escopo do banco de dados ou da instrução, mantendo o nível de compatibilidade do banco de dados como 150 e superior.Disable table variable deferred compilation at the database or statement scope while still maintaining database compatibility level 150 and higher. Para desabilitar a compilação adiada de variável table para todas as execuções de consulta originadas do banco de dados, execute o seguinte exemplo dentro do contexto do banco de dados aplicável:To disable table variable deferred compilation for all query executions originating from the database, execute the following example within the context of the applicable database:

ALTER DATABASE SCOPED CONFIGURATION SET DEFERRED_COMPILATION_TV = OFF;

Para habilitar novamente a compilação adiada de variável table para todas as execuções de consulta originadas do banco de dados, execute o seguinte exemplo dentro do contexto do banco de dados aplicável:To re-enable table variable deferred compilation for all query executions originating from the database, execute the following example within the context of the applicable database:

ALTER DATABASE SCOPED CONFIGURATION SET DEFERRED_COMPILATION_TV = ON;

Você também pode desabilitar a compilação adiada de variável table em uma consulta específica atribuindo DISABLE_DEFERRED_COMPILATION_TV como uma dica de consulta USE HINT.You can also disable table variable deferred compilation for a specific query by assigning DISABLE_DEFERRED_COMPILATION_TV as a USE HINT query hint. Por exemplo:For example:

DECLARE @LINEITEMS TABLE 
    (L_OrderKey INT NOT NULL,
     L_Quantity INT NOT NULL
    );

INSERT @LINEITEMS
SELECT L_OrderKey, L_Quantity
FROM dbo.lineitem
WHERE L_Quantity = 5;

SELECT  O_OrderKey,
    O_CustKey,
    O_OrderStatus,
    L_QUANTITY
FROM    
    ORDERS,
    @LINEITEMS
WHERE   O_ORDERKEY  =   L_ORDERKEY
    AND O_OrderStatus = 'O'
OPTION (USE HINT('DISABLE_DEFERRED_COMPILATION_TV'));

ExemplosExamples

A.A. Declarando uma variável de tabela de tiposDeclaring a variable of type table

O exemplo a seguir cria uma variável table que armazena os valores especificados na cláusula OUTPUT da instrução UPDATE.The following example creates a table variable that stores the values specified in the OUTPUT clause of the UPDATE statement. Seguem duas instruções SELECT que retornam os valores em @MyTableVar e os resultados da operação de atualização na tabela Employee.Two SELECT statements follow that return the values in @MyTableVar and the results of the update operation in the Employee table. Os resultados na coluna INSERTED.ModifiedDate são diferentes dos valores da coluna ModifiedDate na tabela Employee.Results in the INSERTED.ModifiedDate column differ from the values in the ModifiedDate column in the Employee table. Essa diferença é devido ao gatilho AFTER UPDATE, que atualiza o valor de ModifiedDate com a data atual, e está definido na tabela Employee.This difference is because the AFTER UPDATE trigger, which updates the value of ModifiedDate to the current date, is defined on the Employee table. Porém, as colunas retornadas de OUTPUT refletem os dados antes de os gatilhos serem disparados.However, the columns returned from OUTPUT reflect the data before triggers are fired. Para obter mais informações, confira Cláusula OUTPUT (Transact-SQL).For more information, see OUTPUT Clause (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.B. Criando uma função com valor de tabela embutidaCreating an inline table-valued function

O exemplo a seguir retorna uma função com valor de tabela embutida.The following example returns an inline table-valued function. 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.It returns three columns ProductID, Name, and the aggregate of year-to-date totals by store as YTD Total for each product sold to the store.

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.To invoke the function, run this query.

SELECT * FROM Sales.ufn_SalesByStore (602);  

Confira tambémSee also

COLLATE (Transact-SQL)COLLATE (Transact-SQL)
CREATE FUNCTION (Transact-SQL)CREATE FUNCTION (Transact-SQL)
Funções definidas pelo usuárioUser-Defined Functions
CREATE TABLE (Transact-SQL)CREATE TABLE (Transact-SQL)
DECLARE @local_variable (Transact-SQL)DECLARE @local_variable (Transact-SQL)
Usar parâmetros com valor de tabela (Mecanismo de Banco de Dados)Use Table-Valued Parameters (Database Engine)
Query Hints (Transact-SQL) [Dicas de consulta (Transact-SQL)]Query Hints (Transact-SQL)