SQL Server 2016 - Cómo "viajar en el tiempo" usando Temporal Tables

Una tabla temporal no es más que una tabla común, que tiene un sistema de versionado para registrar los datos modificados en una tabla de historial (definida por ti) con dos columnas de tipo DATETIME2 para controlar la validez del registro y así permitir que esta característica funcione. La tabla original siempre mantendrá los datos actuales y la tabla de historial registrará registros antiguos, como se muestra en la siguiente imagen:

Al consultar los datos especificando la fecha deseada, se considerarán los registros de las 2 tablas, de forma transparente para usted, como lo demostraré a continuación en el siguiente tema.

Una parte importante del proceso de implementación de una nueva característica es comprender cómo funciona y sus restricciones, que enumeraré a continuación:

• La tabla en cuestión debe tener una CLAVE PRIMARIA definida para poder utilizar la función de control de versiones. De lo contrario, verá un mensaje como este:
  Mensaje 13553, Nivel 16, Estado 1, Línea 4
  La tabla temporal versionada por el sistema 'dirceuresende.dbo.Table_Temporal' debe tener una clave principal definida.
• Una vez que cree una tabla con esta característica habilitada, no podrá realizar la operación TRUNCATE TABLE en esta tabla, lo que generará el siguiente mensaje de error:
  Mensaje 13545, Nivel 16, Estado 1, Línea 58
  Error al truncar la tabla 'dirceuresende.dbo.Temporal_Table' porque no se admite la operación en tablas con versión del sistema.
• Ya no podrás eliminar la tabla que tiene versiones activas. Para hacer esto, deberá detener el control de versiones y luego eliminar la tabla. De lo contrario, verá el siguiente mensaje de error:
  Mensaje 13552, Nivel 16, Estado 1, Línea 58
  La operación de eliminación de tabla falló en la tabla 'dirceuresende.dbo.Table_Temporal' porque no se admite la operación en tablas temporales con versión del sistema.
• No se puede utilizar OLTP en memoria
• Los desencadenantes EN LUGAR DE no están permitidos. DESPUÉS de los activadores solo se permiten en la tabla actual (no se pueden crear en la tabla histórica).
• La tabla histórica no puede tener restricciones.
  Mensaje 13564, Nivel 16, Estado 1, Línea 1
  No se permite agregar la restricción CHECK a una tabla de historial temporal 'dirceuresende.dbo.Table_Temporal_Historico'.
  Mensaje 1750, Nivel 16, Estado 0, Línea 1
  No se pudo crear una restricción o un índice. Ver errores anteriores.
• Los datos de la tabla histórica no se pueden modificar manualmente (ACTUALIZAR, ELIMINAR, INSERTAR, etc.)
  Mensaje 13560, Nivel 16, Estado 1, Línea 1
  No se pueden eliminar filas de una tabla de historial temporal 'dirceuresende.dbo.Table_Temporal_Historico'.
• Las declaraciones INSERT y UPDATE no pueden hacer referencia a columnas del período SYSTEM_TIME
  Mensaje 13537, Nivel 16, Estado 1, Línea 5
  No se pueden actualizar las columnas GENERADAS SIEMPRE en la tabla 'dirceuresende.dbo.Table_Temporal'.

Para verificar qué tablas tienen activada la función de control de versiones del sistema, simplemente ejecute esta consulta a continuación:

SELECT
    A.[name],
    A.[object_id],
    A.temporal_type,
    A.temporal_type_desc,
    A.history_table_id,
    B.[name]
FROM
    sys.tables A
    LEFT JOIN sys.tables B ON B.[object_id] = A.history_table_id
ORDER BY
    A.temporal_type DESC

Resultado:

Para crear una tabla con el control de versiones del sistema habilitado, deberá crear 2 columnas en su tabla para determinar la validez de los registros:

• Dt_Inicio DATETIME2 GENERADA SIEMPRE COMO INICIO DE FILA
• Dt_Fim DATETIME2 GENERADO SIEMPRE COMO FINAL DE FILA

Y también se agregará la cláusula PERIOD FOR SYSTEM_TIME (Dt_Start, Dt_Fim) después de estas columnas y también la cláusula CON (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Table_Historico)) al final del comando CREATE para su tabla.

Ahora demostraré una CREATE TABLE completa para que sea más fácil entender cómo crear una tabla temporal:

USE [dirceuresende]
GO

CREATE TABLE dbo.Tabela_Temporal (
    
    -- Dados normais da tabela
    Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Ds_Nome VARCHAR(100) NOT NULL,
    Dt_Nascimento DATETIME NOT NULL,
    Nr_Telefone VARCHAR(15) NOT NULL,
    Nr_CPF VARCHAR(14) NOT NULL,

    -- Informações referentes ao versionamento (Temporal table)
    Dt_Inicio DATETIME2(0) GENERATED ALWAYS AS ROW START, 
    Dt_Fim DATETIME2(0) GENERATED ALWAYS AS ROW END, 
    PERIOD FOR SYSTEM_TIME (Dt_Inicio, Dt_Fim)
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Tabela_Temporal_Historico))

Después de crear una tabla temporal (versionado del sistema), la pantalla del Explorador de objetos de SQL Server Management Studio mostrará la tabla de la siguiente manera:

Para convertir una tabla común en una tabla temporal, simplemente ejecute algunos comandos ALTER TABLE para crear las columnas de definición de período (Dt_Start y Dt_End) y activar el control de versiones en la tabla, como se muestra a continuación:

USE [dirceuresende]
GO

-- Criação normal de uma tabela
CREATE TABLE dbo.Tabela_Comum (
    Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Ds_Nome VARCHAR(100) NOT NULL,
    Dt_Nascimento DATETIME NOT NULL,
    Nr_Telefone VARCHAR(15) NOT NULL,
    Nr_CPF VARCHAR(14) NOT NULL,
) WITH(DATA_COMPRESSION=PAGE)


-- Crio as colunas de metadados para controlar a validade dos registros
ALTER TABLE dbo.Tabela_Comum ADD 
    Dt_Inicio DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL, 
    Dt_Fim DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (Dt_Inicio, Dt_Fim)


-- Ativo o versionamento na tabela
ALTER TABLE dbo.Tabela_Comum SET (SYSTEM_VERSIONING=ON (HISTORY_TABLE = dbo.Tabela_Comum_Historico))

¿Qué pasa si la tabla ya tiene registros? Bueno, en este caso simplemente crea las columnas con una restricción para incluir un valor DEFAULT en las columnas que se crearán ahora y que no tienen ningún valor:

USE [dirceuresende]
GO

-- Criação normal de uma tabela
CREATE TABLE dbo.Tabela_Comum (
    Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Ds_Nome VARCHAR(100) NOT NULL,
    Dt_Nascimento DATETIME NOT NULL,
    Nr_Telefone VARCHAR(15) NOT NULL,
    Nr_CPF VARCHAR(14) NOT NULL,
) WITH(DATA_COMPRESSION=PAGE)


INSERT INTO dbo.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
ALTER TABLE dbo.Tabela_Comum ADD 
    Dt_Inicio DATETIME2 GENERATED ALWAYS AS ROW START CONSTRAINT DF_Tabela_Comum_Dt_Inicio DEFAULT SYSUTCDATETIME() NOT NULL, 
    Dt_Fim DATETIME2 GENERATED ALWAYS AS ROW END CONSTRAINT DF_Tabela_Comum_Dt_Fim DEFAULT '9999-12-31 23:59:59.9999999' NOT NULL,
    PERIOD FOR SYSTEM_TIME (Dt_Inicio, Dt_Fim)


-- Ativo o versionamento na tabela
ALTER TABLE dbo.Tabela_Comum SET (SYSTEM_VERSIONING=ON (HISTORY_TABLE = dbo.Tabela_Comum_Historico))

Con la consulta anterior crearás nuevas columnas con Dt_Start = fecha/hora actual y Dt_End = fecha máxima de datetime2, es decir, todos los registros de la tabla se considerarán registros actuales.

Ahora que hemos creado nuestra tabla con el versionado del sistema activado, transformando nuestra tabla en una tabla temporal, generemos algo de información y luego veamos cómo podemos consultarla:

INSERT INTO dbo.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'
)


UPDATE dbo.Tabela_Temporal
SET Ds_Nome = 'Teste'
WHERE Ds_Nome = 'Teste 2'

UPDATE dbo.Tabela_Temporal
SET Nr_CPF = '22222222222'
WHERE Ds_Nome = 'Dirceu Resende'

DELETE FROM dbo.Tabela_Temporal
WHERE Ds_Nome = 'Teste'

Resultado de nuestra tabla:

En los ejemplos anteriores, utilicé la cláusula COMO DE 'datos'. Existen 5 tipos de cláusulas para especificar el rango de fechas deseado:

• TODO: Devuelve todos los cambios realizados en la tabla

  Ejemplo:
  

  

• A PARTIR DE: Función de punto en el tiempo, es decir, devuelve datos en una fecha y hora específicas

  

• DESDE HASTA : Devuelve todos los registros cuyo campo Dt_Inicial < fecha de finalización ingresada y campo Dt_Final > fecha de inicio informada.

  Ejemplo:
  

  

• ENTRE Y : Devuelve todos los registros cuyo campo Dt_Inicial <= fecha de finalización ingresada y campo Dt_Final > fecha de inicio informada. Esta cláusula es muy similar a la cláusula FROM... TO.. con la única diferencia de que la primera condición también considera registros con igualdad en la condición (<=), mientras que la cláusula FROM.. TO.. no (solo <)

  Ejemplo:
  

  

• CONTENIDO EN (, ): Devuelve todos los registros cuyo Dt_Inicial >= fecha inicial ingresada y campo Dt_Final <= fecha final ingresada.

  Ejemplo:
  

  

Si ya no desea utilizar la función de control de versiones del sistema, transformando una tabla temporal en una tabla común y eliminando todos los metadatos y la estructura de la tabla temporal, simplemente use los siguientes comandos:

ALTER TABLE dbo.Tabela_Temporal SET (SYSTEM_VERSIONING = OFF);
ALTER TABLE dbo.Tabela_Temporal DROP PERIOD FOR SYSTEM_TIME;
ALTER TABLE dbo.Tabela_Temporal DROP COLUMN Dt_Inicio, Dt_Fim;

Si solo desea deshabilitar temporalmente la función de control de versiones, pero no desea eliminar la información, puede usar el siguiente comando:

ALTER TABLE dbo.Tabela_Temporal SET (SYSTEM_VERSIONING = OFF);

Una cosa con la que debe tener cuidado al activar esta función en su instancia está relacionada con el espacio en disco consumido por la tabla de historial. De forma predeterminada, la tabla crecerá indefinidamente hasta que se agote el espacio en disco. Para controlar esto, puede crear un trabajo que realizará el proceso de limpieza de datos, manteniendo solo los datos más recientes, como se muestra en los ejemplos siguientes:

Base de datos SQL de Azure

ALTER DATABASE CURRENT SET TEMPORAL_HISTORY_RETENTION ON
GO

ALTER TABLE [dbo].[Tabela_Temporal] SET (SYSTEM_VERSIONING = ON (HISTORY_RETENTION_PERIOD = 30 DAYS))
GO

Servidor SQL (local)

ALTER TABLE dbo.Tabela_Temporal SET (SYSTEM_VERSIONING = OFF)
GO

DELETE FROM dbo.Tabela_Temporal_Historico
WHERE Dt_Fim <= DATEADD(DAY, -30, GETDATE())

ALTER TABLE dbo.Tabela_Temporal
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[Tabela_Temporal_Historico], DATA_CONSISTENCY_CHECK = ON))