Las tablas temporales, también conocidas como tablas con versión del sistema, son una poderosa característica introducida en SQL Server 2016. Permiten a SQL Server mantener automáticamente un historial de los datos en una tabla, proporcionando una forma de consultar datos actualizados y eliminados. En este artículo, exploraremos el concepto de las tablas temporales y cómo crearlas.

¿Qué son las Tablas Temporales?

Una tabla temporal es un tipo de tabla que almacena versiones históricas de datos. A diferencia de una tabla normal, que solo devuelve los datos actuales, una tabla temporal te permite recuperar tanto las versiones actuales como las anteriores de los datos. Esto se logra mediante el mantenimiento de una tabla de historial que almacena los datos antiguos junto con fechas de inicio y fin para indicar cuándo el registro estuvo activo.

Las tablas temporales tienen varios casos de uso, incluyendo:

• Auditoría: Las tablas temporales te permiten rastrear los cambios realizados en una entidad específica a lo largo de su vida útil.
• Dimensiones de cambio lento: Las tablas temporales se comportan como dimensiones con un comportamiento de cambio tipo 2 para todas sus columnas.
• Reparación de corrupciones a nivel de registro: Las tablas temporales actúan como un mecanismo de respaldo para una sola tabla, lo que te permite recuperar registros eliminados accidentalmente de la tabla de historial.

Creando una Tabla Temporal

Al crear una tabla temporal, hay algunos requisitos previos que deben cumplirse:

• Se debe definir una clave primaria.
• Se deben definir dos columnas para registrar las fechas de inicio y fin de los registros.
• No se permiten desencadenadores INSTEAD OF y solo se permiten desencadenadores AFTER en la tabla actual.

Aquí tienes un ejemplo de creación de una tabla con versión del sistema simple:

CREATE TABLE dbo.TestTemporal
(
  ID int primary key,
  A int,
  B int,
  C AS A * B,
  SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
  SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
  PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
) WITH (SYSTEM_VERSIONING = ON);

De forma predeterminada, la tabla de historial está comprimida por página. También hay algunas limitaciones a considerar, como la incapacidad de modificar datos en la tabla de historial y la incapacidad de utilizar declaraciones INSERT y UPDATE que hagan referencia a las columnas del período SYSTEM_TIME.

Consultando una Tabla Temporal

Una vez creada una tabla temporal, puedes consultarla utilizando la nueva cláusula FOR SYSTEM_TIME. Esto te permite especificar un rango de tiempo y recuperar los datos que estuvieron activos durante ese período. Por ejemplo:

SELECT * FROM dbo.TestTemporal
FOR SYSTEM_TIME BETWEEN '2022-01-01' AND '2022-01-31';

Esta consulta devolverá todos los registros de la tabla temporal que estuvieron activos entre el 1 de enero de 2022 y el 31 de enero de 2022.

Modificando una Tabla Temporal

En SQL Server 2016 y 2017, había limitaciones para modificar una tabla temporal. Sin embargo, en SQL Server 2017, muchas de estas limitaciones fueron eliminadas. Ahora es posible modificar el esquema de una tabla temporal utilizando la instrucción ALTER TABLE.

Aquí tienes un ejemplo de desactivar la versión del sistema para modificar el esquema de una tabla temporal:

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

Después de realizar las modificaciones necesarias en la tabla principal y la tabla de historial, puedes volver a habilitar la versión del sistema:

ALTER TABLE dbo.TestTemporal SET (SYSTEM_VERSIONING = ON);

Política de Retención

En SQL Server 2017, se introdujo un nuevo concepto llamado política de retención para las tablas temporales. Una política de retención te permite controlar el crecimiento de la tabla de historial eliminando automáticamente los datos antiguos. Esto es útil para gestionar el tamaño de la tabla de historial, especialmente si los datos se modifican con frecuencia.

Conclusión

Las tablas temporales son una característica valiosa en SQL Server que proporciona una forma de rastrear el historial de datos en una tabla. Tienen varios casos de uso, incluyendo auditoría, dimensiones de cambio lento y recuperación de datos. Al comprender cómo crear y consultar tablas temporales, puedes aprovechar esta característica para mejorar la gestión y el análisis de datos en tu entorno de SQL Server.