¿Cómo utilizar las tablas temporales de SQL Server?

Introducción a las tablas temporales de SQL Server para gestionar automáticamente el versionado de datos.

Las tablas temporales en SQL Server, también conocidas como tablas con control de versiones del sistema, son una característica introducida en MS SQL Server 2016. Proporcionan soporte integrado para almacenar datos históricos relacionados con los cambios realizados en los datos de una tabla. Esta característica es beneficiosa para fines de auditoría o análisis de datos empresariales, donde es crucial comprender el estado de los datos en cualquier momento dado. Y también para todas las empresas sujetas a informes regulatorios regulares con obligaciones de transparencia legal.

Sintaxis básica de la tabla temporal de MS SQL

Comencemos creando una tabla temporal básica en Transact-SQL. Así que el siguiente código T-SQL crea una tabla llamada Employee con control de versiones del sistema. Las columnas SysStartTime y SysEndTime se utilizan para registrar el período de cada fila. Para hacer un pequeño resumen, se necesitan 3 elementos adicionales para crear y gestionar una tabla temporal de SQL Server:

• SysStartTime columna del sistema para marcar el inicio del período, solo admite el tipo de datos DATETIME2.
• SysEndTime para marcar el final, solo admite el tipo de datos DATETIME2.
• Una tabla de historial del sistema, aquí se llama dbo.EmployeeHistory, con la misma estructura, excepto las claves.

La declaración de creación de la tabla es específica

La definición de la tabla es especial, sin embargo, y ese es el objetivo, insertar datos en una tabla con control de versiones del sistema es similar a insertar datos en cualquier otra tabla de SQL Server. De hecho, las tablas con control de versiones del sistema, es decir, las tablas temporales están diseñadas para gestionar automáticamente el versionado de datos, especialmente útil para gestionar dimensiones y dimensiones de cambio lento, llamadas SCD.

CREATE TABLE [Expert-Only].dbo.Employee
(
   EmployeeId INT PRIMARY KEY,
   EmployeeName NVARCHAR(100),
   Position NVARCHAR(100),
   Salary DECIMAL(18, 2),
   SysStartTime  DATETIME2 GENERATED ALWAYS AS ROW START,
   SysEndTime    DATETIME2 GENERATED ALWAYS AS ROW END,
   PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (
   HISTORY_TABLE = dbo.EmployeeHistory)
);

Insertar datos en la tabla temporal

Así que esta básica instrucción T-SQL INSERT insertará una nueva fila en la tabla Employee, y actualizará la tabla de historial asociada cuando cambien los datos.

INSERT INTO [Expert-Only].dbo.Employee (EmployeeId, EmployeeName, Position, Salary)
VALUES (1, 'John Doe', 'Developer', 80000);

Estructura de la tabla con control de versiones del sistema y la tabla de historial

Como puede ver en la captura de pantalla a continuación, la tabla Employee ahora tiene control de versiones del sistema y la tabla EmployeeHistory se encuentra justo detrás de ella en el explorador de SSMS. Ambas tablas tienen la misma estructura, excepto las claves primarias, porque la tabla de historial necesita almacenar múltiples versiones de líneas con los mismos ID.

Actualizar datos de SQL en tablas temporales

Cuando actualiza un registro en una tabla con control de versiones del sistema, SQL Server automáticamente inserta una copia de la fila antigua en la tabla de historial. Así que puede analizar cualquier cambio pasado para entender mejor sus datos. Como resultado, después de ejecutar este comando SQL, la tabla EmployeeHistory tendrá un registro con el salario antiguo. Para ilustrar el concepto, la siguiente consulta activa una inserción con el valor antiguo de la línea, directamente dentro de la tabla de historial.

UPDATE [Expert-Only].dbo.Employee
   SET Salary = 85000
   WHERE EmployeeId = 1;

Eliminar datos de tablas con control de versiones del sistema

Eliminar registros en una tabla con control de versiones del sistema no eliminará los datos por completo. Los registros eliminados se moverán a la tabla de historial. Después de ejecutar el siguiente comando T-SQL, la tabla EmployeeHistory tendrá el registro eliminado.

DELETE FROM Employee
   WHERE EmployeeId = 1;

Por supuesto, debe tener en cuenta la frecuencia con la que cambian los datos y también los volúmenes de datos para evitar terminar con bases de datos y copias de seguridad enormes. Puede verlo como el mismo principio de la Windows Papelera de reciclaje, de hecho, puede tener múltiples versiones del mismo archivo, eliminadas pero aún en la papelera, hasta que la vacíe.

Una solución a esto es implementar un sistema de purga que elimine periódicamente los registros antiguos de la tabla de historial.

Sintaxis de consulta T-SQL para verificar datos de tablas temporales

SQL Server proporciona la cláusula FOR SYSTEM_TIME para consultar datos de una tabla con control de versiones del sistema. Este comando SQL devolverá los datos tal como estaban en el momento especificado. Puede utilizar más detalles, por supuesto, para ser más específico. Puede llegar hasta

SELECT *
  FROM Employee
  FOR SYSTEM_TIME AS OF '2023-10-10';

SELECT *
  FROM Employee
  FOR SYSTEM_TIME AS OF '2023-10-10 15:30:45.1234567';

Prueba un dato temporal para ver cómo funciona

Para realizar una prueba más detallada, repasemos todas las acciones realizadas y añadamos una, en este orden, para ver si la tabla sigue correctamente nuestros cambios. Entre cada paso, espere unos minutos para una mejor visibilidad en la tabla de historial.

1. Insertar la primera línea con un salario de 80’000 dólares (realizado en la segunda sección del tutorial)
2. Actualizar el salario a 85’000 dólares
3. Eliminar el empleado de la tabla.
4. Insertar nuevamente el mismo empleado, pero esta vez con un salario de 90’000 dólares y un rol de Desarrollador Senior.
5. Eliminar nuevamente el empleado de la tabla. No visible en la captura de pantalla pero disponible en el siguiente código T-SQL.

UPDATE [Expert-Only].dbo.Employee
   SET Salary = 85000
   WHERE EmployeeId = 1;

-- *** Esperar unos minutos *** -- 
DELETE FROM [Expert-Only].dbo.Employee
   WHERE EmployeeId = 1;

-- *** Esperar unos minutos *** -- 
INSERT INTO [Expert-Only].dbo.Employee
   (EmployeeId, EmployeeName, Position, Salary)
VALUES (1, 'John Doe', 'Developer Senior', 90000);

-- *** Esperar unos minutos *** -- 
DELETE FROM [Expert-Only].dbo.Employee
   WHERE EmployeeId = 1;

SELECT * FROM dbo.EmployeeHistory;

-- Primer insert
SELECT * FROM [Expert-Only].dbo.Employee
  FOR SYSTEM_TIME AS OF '2023-10-10 10:56:00';

-- Actualización a 85000 $
SELECT * FROM [Expert-Only].dbo.Employee
  FOR SYSTEM_TIME AS OF '2023-10-10 11:03:00';

-- Segundo insert como Desarrollador Senior
SELECT * FROM [Expert-Only].dbo.Employee
  FOR SYSTEM_TIME AS OF '2023-10-10 11:06:00';

Luego, verifique el resultado utilizando SSMS para seleccionar el contenido de la tabla de historial y también la tabla Employee en puntos muy específicos en el tiempo. Como resultado, la tabla Employee ahora está vacía. Las tres líneas archivadas de la tabla EmployeeHistory están disponibles usando una declaración SELECT en la propia tabla Employee en 3 puntos diferentes en el tiempo.

Modificar tablas existentes con control de versiones del sistema

Por supuesto, a medida que los modelos de datos evolucionan en la base de datos con el tiempo, es necesario mantener la tabla de historial cuando cambia la tabla con versión. Para modificar el esquema de una tabla con versión del sistema, por ejemplo, para añadir una nueva columna a la tabla Empleado, sigue estos 3 pasos, en este orden:

1. Desactiva primero la versión del sistema.
2. Haz los cambios en la tabla temporal.
3. Luego reactiva la versión del sistema.

Eliminar una tabla con versión del sistema

Dado que es un tipo específico de tabla, para eliminarla, también necesitas en este caso detener manualmente la versión antes de eliminar la tabla. Para eliminar una tabla con versión del sistema sin error, usa la opción de generación de script de SSMS para generar la instrucción completa. Como puedes ver a continuación, realiza 3 operaciones:

1. Detener la versión.
2. Eliminar la tabla con versión del sistema.
3. Eliminar la tabla temporal que contiene el historial.

Si intentas eliminar la tabla sin detener la versión, el sistema devuelve este error:

Msg 13552, Nivel 16, Estado 1, Línea 1 La operación de eliminación de la tabla falló en ‘Expert-Only.dbo.Employee’ porque no es una operación admitida en tablas temporales con versión del sistema.

---

Conclusión sobre la configuración de tablas temporales en SQL Server

En conclusión, las Tablas Temporales de SQL Server ofrecen una solución integrada para rastrear los cambios de datos a lo largo del tiempo. Pueden ser valiosas en situaciones donde se requiere una auditoría o análisis de datos. Para escenarios reales de empresa, cuando tenías que hacer todos los pasos de archivado con código personalizado o SCD, era complejo y pesado mantenerlo. Usar esta característica de tabla con versión del sistema hace que sea mucho más sencillo implementarlo.

Al comprender cómo crear, insertar, actualizar, eliminar y consultar datos de estas tablas, uno puede aprovechar esta poderosa característica en MS SQL Server. Para profundizar y descubrir más casos de uso para tablas temporales, un ejemplo típico de la vida real es cuando se carga una tabla de dimensión de un almacén de datos utilizando un paquete SSIS.

Para obtener información más detallada sobre cómo trabajar con Tablas Temporales en SQL Server, consulta la documentación oficial de Microsoft.