Tabelas temporaisTemporal tables

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

O SQL Server 2016 introduziu o suporte para tabelas temporais (também conhecidas como tabelas temporais com versão do sistema) como um recurso de banco de dados que oferece suporte interno para fornecer informações sobre os dados armazenados na tabela em qualquer ponto no tempo, em vez de apenas os dados que estão corretos atualmente.SQL Server 2016 introduced support for temporal tables (also known as system-versioned temporal tables) as a database feature that brings built-in support for providing information about data stored in the table at any point in time rather than only the data that is correct at the current moment in time. Temporal é um recurso de banco de dados que foi introduzido no ANSI SQL 2011.Temporal is a database feature that was introduced in ANSI SQL 2011.

Início rápidoQuick-start:

O que é uma tabela temporal com versão do sistemaWhat is a system-versioned temporal table?

Uma tabela temporal com versão do sistema é um tipo de tabela de usuário criada para manter um histórico completo de alterações de dados e permitir uma análise fácil de pontos no tempo.A system-versioned temporal table is a type of user table designed to keep a full history of data changes and allow easy point in time analysis. Esse tipo de tabela temporal é conhecido como tabela temporal com versão do sistema porque o período de validade para cada linha é gerenciado pelo sistema (ou seja, o mecanismo de banco de dados).This type of temporal table is referred to as a system-versioned temporal table because the period of validity for each row is managed by the system (i.e. database engine).

Cada tabela temporal tem duas colunas explicitamente definidas, cada um com um tipo de dados datetime2 .Every temporal table has two explicitly defined columns, each with a datetime2 data type. Essas colunas são chamadas de colunas de período.These columns are referred to as period columns. Essas colunas de período são usadas exclusivamente pelo sistema para gravar o período de validade para cada linha sempre que uma linha é modificada.These period columns are used exclusively by the system to record period of validity for each row whenever a row is modified.

Além dessas colunas de período, uma tabela temporal também contém uma referência a outra tabela com um esquema espelhado.In addition to these period columns, a temporal table also contains a reference to another table with a mirrored schema. O sistema usa essa tabela para armazenar a versão anterior da linha automaticamente sempre que uma linha na tabela temporal é atualizada ou excluída.The system uses this table to automatically store the previous version of the row each time a row in the temporal table gets updated or deleted. Essa tabela adicional é conhecida como a tabela de histórico, enquanto a tabela principal que armazena as versões atuais (reais) das linhas é conhecida como a tabela atual ou simplesmente como a tabela temporal.This additional table is referred to as the history table, while the main table that stores current (actual) row versions is referred to as the current table or simply as the temporal table. Durante a criação da tabela temporal, os usuários podem especificar uma tabela de histórico existente (deve ter um esquema compatível) ou deixar que o sistema crie a tabela de histórico padrão.During temporal table creation users can specify existing history table (must be schema compliant) or let system create default history table.

Por que temporalWhy temporal?

As fontes de dados reais são dinâmicas e geralmente as decisões comerciais dependem das informações que os analistas podem obter da evolução dos dados.Real data sources are dynamic and more often than not business decisions rely on insights that analysts can get from data evolution. Os casos de uso de tabelas temporais incluem:Use cases for temporal tables include:

  • Auditar todas as alterações de dados e executar análise forense de dados quando necessárioAuditing all data changes and performing data forensics when necessary
  • Reconstruir o estado dos dados a partir de qualquer momento no passadoReconstructing state of the data as of any time in the past
  • Calcular tendências ao longo do tempoCalculating trends over time
  • Manter uma dimensão de alteração lenta para aplicações de suporte a decisõesMaintaining a slowly changing dimension for decision support applications
  • Recuperar alterações acidentais em dados e erros de aplicativosRecovering from accidental data changes and application errors

Como funciona a tabela temporalHow does temporal work?

A versão do sistema para uma tabela é implementada como um par de tabelas, uma tabela atual e uma tabela de histórico.System-versioning for a table is implemented as a pair of tables, a current table and a history table. Dentro de cada uma dessas tabelas, as seguintes colunas datetime2 adicionais são usadas para definir o período de validade para cada registro:Within each of these tables, the following two additional datetime2 columns are used to define the period of validity for each row:

  • Coluna de início do período: O sistema registra a hora de início da linha na coluna, normalmente denotada como a coluna SysStartTime.Period start column: The system records the start time for the row in this column, typically denoted as the SysStartTime column.
  • Coluna de término do período: O sistema registra a hora de término da linha na coluna, normalmente indicada como a coluna SysEndTime.Period end column: The system records the end time for the row in this column, typically denoted at the SysEndTime column.

A tabela atual contém o valor atual para cada linha.The current table contains the current value for each row. A tabela de histórico contém cada valor anterior para cada linha, se houver, e a hora de início e término do período para o qual ela era válida.The history table contains each previous value for each row, if any, and the start time and end time for the period for which it was valid.

Temporal-HowWorksTemporal-HowWorks

O exemplo simples a seguir ilustra um cenário com informações de Funcionário em um banco de dados de RH hipotético:The following simple example illustrates a scenario with Employee information in hypothetical HR database:

CREATE TABLE dbo.Employee
(
  [EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED
  , [Name] nvarchar(100) NOT NULL
  , [Position] varchar(100) NOT NULL
  , [Department] varchar(100) NOT NULL
  , [Address] nvarchar(1024) NOT NULL
  , [AnnualSalary] decimal (10,2) NOT NULL
  , [ValidFrom] datetime2 GENERATED ALWAYS AS ROW START
  , [ValidTo] datetime2 GENERATED ALWAYS AS ROW END
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
 )
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));
  • INSERTS: Em uma INSERT, o sistema define o valor para a coluna SysStartTime como a hora de início da transação atual (no fuso horário UTC) com base no relógio do sistema e atribui o valor para a coluna SysEndTime para o valor máximo de 9999-12-31. INSERTS: On an INSERT, the system sets the value for the SysStartTime column to the begin time of the current transaction (in the UTC time zone) based on the system clock and assigns the value for the SysEndTime column to the maximum value of 9999-12-31. Isso marca a linha como aberta.This marks the row as open.
  • UPDATES: Em uma UPDATE, o sistema armazena o valor anterior do registro na tabela de histórico e define o valor para a coluna SysEndTime como a hora de início da transação atual (no fuso horário UTC) com base no relógio do sistema. UPDATES: On an UPDATE, the system stores the previous value of the row in the history table and sets the value for the SysEndTime column to the begin time of the current transaction (in the UTC time zone) based on the system clock. Isso marca a linha como fechada, com um período registrado para o qual a linha era válida.This marks the row as closed, with a period recorded for which the row was valid. Na tabela atual, o registro é atualizado com o novo valor e o sistema define o valor para a coluna SysStartTime para a hora de início da transação (no fuso horário UTC) com base no relógio do sistema.In the current table, the row is updated with its new value and the system sets the value for the SysStartTime column to the begin time for the transaction (in the UTC time zone) based on the system clock. O valor de registro atualizado na tabela atual para a coluna SysEndTime permanece o valor máximo de 9999-12-31.The value for the updated row in the current table for the SysEndTime column remains the maximum value of 9999-12-31.
  • DELETES: Em uma DELETE, o sistema armazena o valor anterior do registro na tabela de histórico e define o valor para a coluna SysEndTime como a hora de início da transação atual (no fuso horário UTC) com base no relógio do sistema. DELETES: On a DELETE, the system stores the previous value of the row in the history table and sets the value for the SysEndTime column to the begin time of the current transaction (in the UTC time zone) based on the system clock. Isso marca a linha como fechada, com um período registrado para o qual a linha anterior era válida.This marks the row as closed, with a period recorded for which the previous row was valid. Na tabela atual, a linha é removida.In the current table, the row is removed. As consultas da tabela atual não retornarão essa linha.Queries of the current table will not return this row. Somente as consultas que lidam com dados de histórico retornarão dados cujo linha está fechada.Only queries that deal with history data return data for which a row is closed.
  • MERGE: Em uma MERGE, a operação se comporta exatamente como se até três instruções (uma INSERT, uma UPDATE e/ou uma DELETE) fossem executadas, dependendo do que é especificado como ações na instrução MERGE. MERGE: On a MERGE, the operation behaves exactly as if up to three statements (an INSERT, an UPDATE, and/or a DELETE) executed, depending on what is specified as actions in the MERGE statement.

Importante

As horas registradas nas colunas datetime2 do sistema baseiam-se na hora de início da própria transação.The times recorded in the system datetime2 columns are based on the begin time of the transaction itself. Por exemplo, todas as linhas inseridas em uma única transação terão o mesmo horário UTC registrado na coluna correspondente ao início do período SYSTEM_TIME .For example, all rows inserted within a single transaction will have the same UTC time recorded in the column corresponding to the start of the SYSTEM_TIME period.

Como faço para consultar dados temporaisHow do I query temporal data?

A cláusula FROM <table> da instrução SELECT tem uma nova cláusula FOR SYSTEM_TIME com cinco subcláusulas temporais específicas para consultar dados das tabelas atual e histórica.The SELECT statement FROM<table> clause has a new clause FOR SYSTEM_TIME with five temporal-specific sub-clauses to query data across the current and history tables. Essa nova sintaxe de instrução SELECT é suportada diretamente em uma única tabela, propagada por meio de várias associações e exibições em várias tabelas temporais.This new SELECT statement syntax is supported directly on a single table, propagated through multiple joins, and through views on top of multiple temporal tables.

Temporal-QueryingTemporal-Querying

A consulta a seguir procura por versões de linha da linha Employee com EmployeeID = 1000 que estavam ativas durante, pelo menos, parte do período entre 1º de janeiro de 2014 e 1º de janeiro de 2015 (incluindo o limite superior):The following query searches for row versions for Employee row with EmployeeID = 1000 that were active at least for a portion of period between 1st January of 2014 and 1st January 2015 (including the upper boundary):

SELECT * FROM Employee
  FOR SYSTEM_TIME
    BETWEEN '2014-01-01 00:00:00.0000000' AND '2015-01-01 00:00:00.0000000'
      WHERE EmployeeID = 1000 ORDER BY ValidFrom;

Observação

FOR SYSTEM_TIME filtra as linhas que têm um período de validade com duração zero (SysStartTime = SysEndTime). FOR SYSTEM_TIME filters out rows that have period of validity with zero duration (SysStartTime SysEndTime). Essas linhas serão geradas se você realizar várias atualizações na mesma chave primária na mesma transação.Those rows will be generated if you perform multiple updates on the same primary key within the same transaction. Nesse caso, a consulta temporal exibe somente versões de linha anteriores às transações e que se tornaram reais após as transações.In that case, temporal querying surfaces only row versions before the transactions and ones that became actual after the transactions. Se você precisa incluir as linhas na análise, veja diretamente a tabela de histórico.If you need to include those rows in the analysis, query the history table directly.

Na tabela a seguir, SysStartTime na coluna Qualifying Rows representa o valor na coluna SysStartTime da tabela que está sendo consultada e SysEndTime representa o valor da coluna SysEndTime da tabela que está sendo consultada.In the table below, SysStartTime in the Qualifying Rows column represents the value in the SysStartTime column in the table being queried and SysEndTime represents the value in the SysEndTime column in the table being queried. Para obter a sintaxe completa e exemplos, veja FROM (Transact-SQL) e Consultando dados em uma tabela temporal com controle da versão do sistema.For the full syntax and for examples, see FROM (Transact-SQL) and Querying Data in a System-Versioned Temporal Table.

ExpressionExpression Linhas de qualificaçãoQualifying Rows DESCRIÇÃODescription
AS OF<date_time>
AS OF<date_time> SysStartTime <= date_time AND SysEndTime > date_timeSysStartTime <= date_time AND SysEndTime > date_time Retorna uma tabela com uma linha que contém os valores que foram reais (atuais) no momento especificado no passado.Returns a table with a rows containing the values that were actual (current) at the specified point in time in the past. Internamente, uma união é executada entre a tabela temporal e sua tabela de histórico e os resultados são filtrados para retornar os valores na linha que era válida no ponto no tempo especificado pelo parâmetro <date_time> .Internally, a union is performed between the temporal table and its history table and the results are filtered to return the values in the row that was valid at the point in time specified by the <date_time> parameter. O valor de uma linha é considerado válido se o valor de system_start_time_column_name é menor ou igual ao valor do parâmetro <date_time> e o valor de system_end_time_column_name é maior que o valor do parâmetro <date_time> .The value for a row is deemed valid if the system_start_time_column_name value is less than or equal to the date_time> parameter value and the system_end_time_column_name value is greater than the date_time> parameter value.
FROM<start_date_time>TO<end_date_time>
FROM<start_date_time>TO<end_date_time> SysStartTime < end_date_time AND SysEndTime > start_date_timeSysStartTime < end_date_time AND SysEndTime > start_date_time Retorna uma tabela com os valores para todas as versões de linha que estavam ativas no intervalo de tempo especificado, não importando se eles começaram a ser ativos antes do valor de parâmetro <start_date_time> para o argumento FROM ou deixaram de ser ativos após o valor de parâmetro <end_date_time> para o argumento TO.Returns a table with the values for all row versions that were active within the specified time range, regardless of whether they started being active before the <start_date_time> parameter value for the FROM argument or ceased being active after the <end_date_time> parameter value for the TO argument. Internamente, uma união é executada entre a tabela temporal e sua tabela de histórico e os resultados são filtrados para retornar os valores para todas as versões de linha que estavam ativas a qualquer momento durante o intervalo de tempo especificado.Internally, a union is performed between the temporal table and its history table and the results are filtered to return the values for all row versions that were active at any time during the time range specified. As linhas que deixaram de ser ativas exatamente no limite inferior definido pelo ponto de extremidade FROM não são incluídas e os registros que se tornaram ativos exatamente no limite superior definido pelo ponto de extremidade TO também não são incluídos.Rows that ceased being active exactly on the lower boundary defined by the FROM endpoint are not included and records that became active exactly on the upper boundary defined by the TO endpoint are not included also.
BETWEEN<start_date_time>AND<end_date_time>
BETWEEN<start_date_time>AND<end_date_time> SysStartTime <= end_date_time AND SysEndTime > start_date_timeSysStartTime <= end_date_time AND SysEndTime > start_date_time A mesma descrição acima para FOR SYSTEM_TIME FROM <start_date_time>TO <end_date_time> é válida, exceto que a tabela de linhas retornada inclui linhas que se tornaram ativas no limite superior definido pelo ponto de extremidade <end_date_time>.Same as above in the FOR SYSTEM_TIME FROM <start_date_time>TO <end_date_time> description, except the table of rows returned includes rows that became active on the upper boundary defined by the <end_date_time> endpoint.
CONTAINED IN (<start_date_time> , <end_date_time>)CONTAINED IN (start_date_time> , end_date_time>) SysStartTime >= start_date_time AND SysEndTime <= end_date_timeSysStartTime >= start_date_time AND SysEndTime <= end_date_time Retorna uma tabela com os valores para todas as versões de linha que foram abertas e fechadas dentro do intervalo de tempo especificado definido por dois valores de data e hora para o argumento CONTAINED IN.Returns a table with the values for all row versions that were opened and closed within the specified time range defined by the two datetime values for the CONTAINED IN argument. As linhas que se tornaram ativas exatamente no limite inferior ou que deixaram de ser ativas exatamente no limite superior são incluídas.Rows that became active exactly on the lower boundary or ceased being active exactly on the upper boundary are included.
ALL'ALL'] Todas as linhasAll rows Retorna a união de linhas que pertencem às tabelas atual e de histórico.Returns the union of rows that belong to the current and the history table.

Observação

Opcionalmente, é possível optar por ocultar essas colunas de período, de modo que as consultas que referenciam explicitamente essas colunas não as retornarão (o cenário SELECT * FROM <table> ).Optionally, you can choose to hide these period columns such that queries that do not explicitly reference these period columns do not return these columns (the SELECT FROM table scenario). Para retornar uma coluna oculta, basta fazer referência explícita à coluna oculta na consulta.To return a hidden column, simply explicitly refer to the hidden column in the query. Da mesma forma, as instruções INSERT e BULK INSERT continuarão como se as novas colunas de período não estivessem presentes (e os valores da coluna serão populados automaticamente).Similarly INSERT and BULK INSERT statements will continue as if these new period columns were not present (and the column values will be auto-populated). Para obter detalhes sobre como usar a cláusula HIDDEN , veja CREATE TABLE (Transact-SQL) e ALTER TABLE (Transact-SQL).For details on using the HIDDEN clause, see CREATE TABLE (Transact-SQL) and ALTER TABLE (Transact-SQL).

Próximas etapasNext steps