Ao consultar os dados especificando a data desejada, os registros das 2 tabelas serĂ£o considerados, de forma transparente para vocĂª, conforme vou demonstrar logo a seguir no prĂ³ximo tĂ³pico.
A tabela em questĂ£o deve ter uma PRIMARY KEY definida para poder utilizar o recurso de versionamento. Caso contrĂ¡rio, vocĂª verĂ¡ uma mensagem como essa:
Msg 13553, Level 16, State 1, Line 4
System versioned temporal table ‘dirceuresende.dbo.Tabela_Temporal’ must have primary key defined.
Uma vez que vocĂª crie uma tabela com esse recurso ativado, vocĂª nĂ£o poderĂ¡ realizar a operaĂ§Ă£o de TRUNCATE TABLE nesta tabela, resultando na seguinte mensagem de erro:
Msg 13545, Level 16, State 1, Line 58
Truncate failed on table ‘dirceuresende.dbo.Tabela_Temporal’ because it is not supported operation on system-versioned tables.
VocĂª nĂ£o conseguirĂ¡ mais excluir a tabela que estĂ¡ com um versionamento ativo. Para fazer isso, precisarĂ¡ parar o versionamento e depois excluir a tabela. Caso contrĂ¡rio, verĂ¡ a seguinte mensagem de erro:
Msg 13552, Level 16, State 1, Line 58
Drop table operation failed on table ‘dirceuresende.dbo.Tabela_Temporal’ because it is not supported operation on system-versioned temporal tables.
In-memory OLTP nĂ£o pode ser utilizado
Triggers INSTEAD OF nĂ£o sĂ£o permitidas. Triggers AFTER sĂ³ permitidas apenas na tabela atual (NĂ£o pode criar na tabela histĂ³rica).
A tabela histĂ³rica nĂ£o pode possuir constraints
Msg 13564, Level 16, State 1, Line 1
Adding CHECK constraint to a temporal history table ‘dirceuresende.dbo.Tabela_Temporal_Historico’ is not allowed.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint or index. See previous errors.
Os dados na tabela histĂ³rica nĂ£o podem ser modificados manualmente (UPDATE, DELETE, INSERT, etc)
Msg 13560, Level 16, State 1, Line 1
Cannot delete rows from a temporal history table ‘dirceuresende.dbo.Tabela_Temporal_Historico’.
As instruções INSERT e UPDATE nĂ£o podem fazer referĂªncia Ă s colunas do perĂodo SYSTEM_TIME
Msg 13537, Level 16, State 1, Line 5
Cannot update GENERATED ALWAYS columns in table ‘dirceuresende.dbo.Tabela_Temporal’.
Quais tabelas sĂ£o temporais na minha base?
Visualizar conteĂºdo
Para verificar quais tabelas estĂ£o com o recurso do versionamento de sistema ativado, basta executar essa query abaixo:
ApĂ³s criar uma tabela temporal (versionamento de sistema), a tela do Object Explorer do SQL Server Management Studio irĂ¡ mostrar a tabela da seguinte maneira:
Como converter uma tabela comum para Tabela Temporal?
Visualizar conteĂºdo
Para converter uma tabela comum para Tabela Temporal, basta executar alguns comandos de ALTER TABLE para criar as colunas de definiĂ§Ă£o de perĂodo (Dt_Inicio e Dt_Fim) e ativar o versionamento na tabela, conforme exemplo abaixo:
Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
USE[dirceuresende]
GO
-- CriaĂ§Ă£o normal de uma tabela
CREATETABLEdbo.Tabela_Comum (
IdINTIDENTITY(1,1)NOTNULLPRIMARYKEY,
Ds_NomeVARCHAR(100)NOTNULL,
Dt_NascimentoDATETIMENOTNULL,
Nr_TelefoneVARCHAR(15)NOTNULL,
Nr_CPFVARCHAR(14)NOTNULL,
)WITH(DATA_COMPRESSION=PAGE)
-- Crio as colunas de metadados para controlar a validade dos registros
E se a tabela jĂ¡ possuir registros ? Bom, neste caso, basta criar as colunas com uma constraint para incluir um valor DEFAULT nas colunas que serĂ£o criadas agora e que nĂ£o possuem valor:
Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
USE[dirceuresende]
GO
-- CriaĂ§Ă£o normal de uma tabela
CREATETABLEdbo.Tabela_Comum (
IdINTIDENTITY(1,1)NOTNULLPRIMARYKEY,
Ds_NomeVARCHAR(100)NOTNULL,
Dt_NascimentoDATETIMENOTNULL,
Nr_TelefoneVARCHAR(15)NOTNULL,
Nr_CPFVARCHAR(14)NOTNULL,
)WITH(DATA_COMPRESSION=PAGE)
INSERTINTOdbo.Tabela_Comum
(
Ds_Nome,
Dt_Nascimento,
Nr_Telefone,
Nr_CPF
)
VALUES
(
'Dirceu Resende',-- Ds_Nome - varchar(100)
'1990-01-01',-- Dt_Nascimento - datetime
'2799999999',-- Nr_Telefone - varchar(15)
'11111111111'-- Nr_CPF - varchar(14)
)
-- Crio as colunas de metadados para controlar a validade dos registros
Com a query acima, vocĂª irĂ¡ criar as novas colunas com Dt_Inicio = data/hora atual e Dt_Fim = data mĂ¡xima do datetime2, ou seja, todos os registros da tabela serĂ£o considerados os registros atuais.
Como consultar os dados da Tabela Temporal?
Visualizar conteĂºdo
Agora que criamos a nossa tabela com o versionamento de sistema ativado, transformando nossa tabela em uma tabela temporal ou temporal table, vamos gerar algumas informações e depois visualizar como podemos consultĂ¡-las:
Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
INSERTINTOdbo.Tabela_Temporal
(
Ds_Nome,
Dt_Nascimento,
Nr_Telefone,
Nr_CPF
)
VALUES
(
'Dirceu Resende',-- Ds_Nome - varchar(100)
'1900-05-28',-- Dt_Nascimento - datetime
'2799999999',-- Nr_Telefone - varchar(15)
'12345678909'
),
(
'Teste 2',-- Ds_Nome - varchar(100)
'1900-01-01',-- Dt_Nascimento - datetime
'27888888888',-- Nr_Telefone - varchar(15)
'11111111111'
)
UPDATEdbo.Tabela_Temporal
SETDs_Nome='Teste'
WHEREDs_Nome='Teste 2'
UPDATEdbo.Tabela_Temporal
SETNr_CPF='22222222222'
WHEREDs_Nome='Dirceu Resende'
DELETEFROMdbo.Tabela_Temporal
WHEREDs_Nome='Teste'
Resultado da nossa tabela:
Nos exemplos acima, utilizei a clĂ¡usula AS OF ‘data’. Existem 5 tipos de clĂ¡usulas para especificar o perĂodo de datas desejado:
ALL: Retorna todas as alterações realizadas na tabela
Exemplo:
AS OF: Recurso point-in-time, ou seja, retorna os dados em uma data e hora especĂfica
FROM <start_date_time> TO <end_date_time>: Retorna todos os registros cujo campo Dt_Inicial < data final informada e campo Dt_Final > data inicial informada.
CONTAINED IN (<start_date_time> , <end_date_time>): Retorna todos os registros cujo Dt_Inicial >= data inicial informada e campo Dt_Final <= data final informada.
Exemplo:
Como desativar o versionamento de uma Tabela Temporal?
Visualizar conteĂºdo
Caso vocĂª nĂ£o queira mais utilizar o recurso de versionamento de sistema, transformando uma tabela temporal numa tabela comum e excluindo todos os metadados e estrutura da tabela temporal, basta vocĂª utilizar os comandos abaixo:
Se vocĂª quer apenas desativar temporariamente o recurso de versionamento, mas nĂ£o quer excluir as informações, vocĂª pode utilizar o comando abaixo:
Dirceu, nos testes realizados aqui, notei que o campo dt_ini esta sempre recebendo a datahora + 4 toda vez que o registro sofre um update.
ele pega a hora atual e soma 4 horas e joga no campo.
isso ocorre tambem na tabela de histĂ³rico,
teste com sql 2016 express
sql instalado local, verifiquei com select getdate() e a data esta correta.
Dirceu, como vocĂª diferencia esse recurso do Change Tracking e CDC? Todos esses trĂªs recursos me parecem atingir o mesmo resultado que a temporal, o que muda Ă© sĂ³ a forma de acessar tais dados histĂ³ricos. Grato pela atenĂ§Ă£o.
OlĂ¡ Rodrigo, vou criar um novo artigo pra responder sua dĂºvida