SQL Server 2016 introdujo una nueva característica llamada Tablas Temporales, que te permite mantener un registro histórico de todas las versiones de cada fila en una tabla. Esta característica, también conocida como tablas con versión del sistema, es útil para realizar un seguimiento de los cambios en los datos a lo largo del tiempo y recuperar información histórica.

Sin embargo, para las tablas con una alta actividad de inserción/eliminación o filas actualizadas con frecuencia, la tabla de historial puede crecer rápidamente sin control. En estos casos, es importante gestionar la retención de los datos históricos de manera efectiva.

El artículo de MSDN, “Gestionar la retención de datos históricos en tablas temporales con versión del sistema”, proporciona algunas opciones para gestionar los datos históricos:

• Base de datos estirada (Stretch Database)
• Particionamiento de tablas (ventana deslizante)
• Script personalizado de limpieza

Aunque estas soluciones son adecuadas para muchos escenarios, es posible que no cubran todos los requisitos. Por ejemplo, ¿qué ocurre si solo quieres mantener las últimas tres versiones de una fila, independientemente de cuándo se realizaron esas modificaciones? ¿O qué ocurre si quieres mantener todas las versiones anteriores de las últimas dos semanas o del año calendario actual, más una versión adicional anterior a eso?

En este artículo, exploraremos cómo lograr la eliminación o archivo selectivo de datos históricos utilizando criterios personalizados. Utilizaremos una tabla base llamada “Empleados” como ejemplo:

CREATE TABLE dbo.Empleados
(
  IDEmpleado int PRIMARY KEY,
  Nombre     nvarchar(64),
  Apellido   nvarchar(64),
  Salario    int,
  ValidoDesde datetime2(7) GENERATED ALWAYS AS ROW START NOT NULL,
  ValidoHasta datetime2(7) GENERATED ALWAYS AS ROW END   NOT NULL,
             PERIOD FOR SYSTEM_TIME (ValidoDesde, ValidoHasta)
) WITH (SYSTEM_VERSIONING = OFF);

INSERT dbo.Empleados(IDEmpleado, Nombre, Apellido, Salario)
  VALUES (1, N'Bobby',  N'Orr',     25000),
         (2, N'Milt',   N'Schmidt', 25000),
         (3, N'Eddie',  N'Shore',   25000);

Una vez configurada la tabla base, podemos crear una tabla de historial con columnas coincidentes:

CREATE TABLE dbo.Empleados_Historial
(
  IDEmpleado int NOT NULL,
  Nombre     nvarchar(64),
  Apellido   nvarchar(64),
  Salario    int,
  ValidoDesde datetime2(7) NOT NULL,
  ValidoHasta datetime2(7) NOT NULL
);  

CREATE CLUSTERED INDEX IDEmpleado_Desde_Hasta 
  ON dbo.Empleados_Historial(IDEmpleado, ValidoDesde, ValidoHasta);

Ahora, podemos poblar la tabla de historial con algunas versiones históricas de las filas:

INSERT dbo.Empleados_Historial
(
  IDEmpleado, Nombre, Apellido, Salario, ValidoDesde, ValidoHasta
)
SELECT IDEmpleado, Nombre, Apellido, 20000, DATEADD(YEAR, -1, ValidoDesde), ValidoDesde
FROM dbo.Empleados;

INSERT dbo.Empleados_Historial
(
  IDEmpleado, Nombre, Apellido, Salario, ValidoDesde, ValidoHasta
)
SELECT IDEmpleado, Nombre, Apellido, 15000, DATEADD(YEAR, -1, ValidoDesde), ValidoDesde
FROM dbo.Empleados_Historial
UNION ALL 
SELECT 4, N'Phil', N'Esposito', 24500, '20150101', '20161231';

INSERT dbo.Empleados(IDEmpleado, Nombre, Apellido, Salario)
  VALUES(5, N'Brad', N'Marchand', 22750);

Con la tabla base y la tabla de historial configuradas, podemos habilitar la versión del sistema:

ALTER TABLE dbo.Empleados SET (SYSTEM_VERSIONING = ON
(
  HISTORY_TABLE = dbo.Empleados_Historial,
  DATA_CONSISTENCY_CHECK = ON
));

Ahora, supongamos que queremos eliminar todas las versiones históricas excepto la más reciente para cada fila. Podemos utilizar una expresión de tabla común (CTE) para identificar las filas a eliminar:

;WITH h AS 
(
  SELECT *, rn = ROW_NUMBER() OVER 
    (PARTITION BY IDEmpleado ORDER BY ValidoDesde DESC)
  FROM dbo.Empleados_Historial
)
DELETE h WHERE rn > 1;

Es importante tener en cuenta que al eliminar filas de la tabla de historial, la versión del sistema debe desactivarse temporalmente. Esto se puede hacer dentro de una transacción serializable para minimizar el impacto en la tabla base. La secuencia de pasos sería:

1. Iniciar una transacción serializable
2. Desactivar la versión del sistema en la tabla base
3. Eliminar las filas de la tabla de historial
4. Volver a activar la versión del sistema
5. Confirmar la transacción

Aquí tienes un ejemplo de cómo se vería el código:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;

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

;WITH h AS 
(
  SELECT *, rn = ROW_NUMBER() OVER 
    (PARTITION BY IDEmpleado ORDER BY ValidoDesde DESC)
  FROM dbo.Empleados_Historial
)
DELETE h WHERE rn > 1;

ALTER TABLE dbo.Empleados SET (SYSTEM_VERSIONING = ON
(
  HISTORY_TABLE = dbo.Empleados_Historial,
  DATA_CONSISTENCY_CHECK = ON
));

COMMIT TRANSACTION;

Utilizando criterios personalizados y eliminación selectiva, puedes gestionar de manera efectiva la retención de datos históricos en SQL Server. Esto te permite mantener solo el historial que necesitas, reduciendo los requisitos de almacenamiento y mejorando el rendimiento.

Las tablas temporales proporcionan una forma poderosa de realizar un seguimiento de los cambios en los datos a lo largo del tiempo, y con la capacidad de gestionar los datos históricos de manera efectiva, puedes optimizar el uso de los recursos del sistema y mantener una base de datos limpia y eficiente.

Recuerda probar y validar tus políticas de retención para asegurarte de que se están eliminando o archivando las filas correctas. Además, considera implementar lógica de lotes para minimizar el impacto en el registro de transacciones y el bloqueo.

Aprovechando las capacidades de SQL Server, puedes mantener un registro histórico completo mientras gestionas de manera eficiente el almacenamiento y el rendimiento de tu base de datos.