Cómo usar la tabla temporal de SQL Server

Las tablas temporales, también conocidas como tablas aversión al sistema, son tablas que le permiten rastrear y mantener un historial de los datos en una tabla específica. Usando tablas temporales, puede rastrear el historial de los cambios realizados en los datos en una tabla.

Este artículo sentará una base para crear, trabajar y usar tablas temporales en SQL Server.

Las tablas versionadas del sistema se introdujeron en el estándar ANSI SQL 2011 y han estado disponibles como una característica en SQL Server 2016 y superior.

A diferencia de una tabla normal que solo puede mostrar y trabajar con los datos actuales, las tablas temporales le permiten ver y trabajar incluso con datos previamente eliminados. Como se mencionó, esto es posible debido a la capacidad de una tabla temporal para realizar un seguimiento de los cambios realizados en los datos en una tabla.

La tabla contiene dos columnas clave: sysstarttime y sysendtime. Estas dos columnas se utilizan para definir los datos existentes y anteriores para cada registro en una tabla. Puede usar intervalos de tiempo específicos para ver cómo han cambiado los datos en una tabla.

Crear una mesa temporal

Antes de que pueda crear una tabla temporal, debe cumplir con los siguientes requisitos:

1. Una tabla temporal debe contener una restricción de clave principal definida.
2. Debe contener dos columnas para registrar la fecha de inicio y finalización. Estas columnas deben ser del tipo de datos DateTime2. Las columnas deben declararse como se generan siempre como inicio/finalización de la fila.
3. SQL Server supone que las dos columnas no son anulables. Por lo tanto, la instrucción Crear tabla falla si la consulta intenta establecer columnas que sean anulables.
4. SQL Server genera automáticamente una tabla de historial utilizando un esquema similar que la tabla temporal.
5. No puede usar en lugar de desencadenantes en una tabla de versiones de sistema.
6. La tabla de historial no debe contener restricciones.
7. No puede cambiar los datos en la tabla de historial.
8. Las declaraciones, como la inserción y la actualización, no pueden hacer referencia a las columnas del período.
9. La tabla de historial se crea como una tabla de historial de filas, y la compresión de la página se aplica si corresponde. De lo contrario, la tabla queda sin comprimir.
10. SQL Server generará automáticamente un índice agrupado para la tabla de historial.

Cómo crear una tabla temporal: T-SQL

Veamos una demostración simple de crear una mesa temporal. Considere la consulta de ejemplo que se muestra a continuación:

Crear tabla DBO.my_temporal_table (
Yo dint,
Fname Varchar (50),
Envíe un correo electrónico a Varchar (255),
Departamento Varchar (50),
Restricción PK Clave primaria (ID),
Sysstarttime dateTime2 generado siempre como la fila no es nulo,
Sysendtime datetime2 generado siempre como el extremo de la fila no nulo,
Período para System_Time (SysStartTime, Sysendtime)) con (System_Versioning = ON);

Una vez que ejecutemos la consulta anterior, el servidor SQL creará la tabla con el nombre especificado.

En el SQL Server Management Studio, puede ver una tabla de versiones del sistema expandiendo la opción Tablas en su base de datos de destino:

Observe que el servidor SQL genera automáticamente una tabla de historial con un esquema similar a la tabla versionada del sistema. Sin embargo, preste atención a las columnas en la tabla de historial. Observe que no tienen ninguna restricción.

Considere la imagen que se muestra a continuación:

Como verá, SQL Server genera una tabla de historial bajo un nombre después de un formato específico. Para establecer un nombre personalizado para su tabla de historial, especifíquelo en la instrucción Crear tabla como se muestra:

---
Período para System_Time (SysStartTime, SysendTime)) con (System_Versioning = ON, History_Table = mytemporal_tableHistory);
---

A continuación, si se expande en la opción de índices para la tabla de historial, nota que SQL Server generó automáticamente un índice agrupado:

Usando tablas temporales

Probemos la funcionalidad de las tablas temporales insertando algunos registros en la tabla. Considere la consulta de ejemplo que se muestra a continuación:

Insertar en my_temporal_table (id, fname, correo electrónico, departamento)
Valores (1, 'John Davis', [email protected] ',' front-end '),
(2, 'Ruby Raw', '[email protected] ',' base de datos '),
(3, 'Scott Turner', '[email protected] ',' Full-Stack '),
(4, 'Alice Jensen', '[email protected] ',' Control de versiones '),
(5, 'Peter Green', '[email protected] ',' Backend ');

Una vez que tenemos los datos de muestra insertados en la tabla, podemos consultarlos como:

Seleccionar * de my_temporal_table;

Debe obtener una salida cerca de la que se muestra a continuación como

Para comprender cómo funciona la tabla versionada del sistema, eliminemos y actualicemos filas en la tabla:

Eliminar de my_temporal_table donde departamento = 'base de datos';
Actualizar my_temporal_table set fname = 'John m' donde id = 5;

A continuación, consulte los datos en la tabla principal:

Seleccionar * de my_temporal_table;

Si consulta la tabla de historial, debería ver la versión anterior de los datos con las marcas de tiempo correctas.

Conclusión

Esta guía cubrió el concepto de tabla de versiones temporales o del sistema en SQL Server. Usando esta guía, estará en condiciones de rastrear el historial de sus datos utilizando tablas temporales de SQL Server. Esperamos que hayas encontrado este artículo útil. Echa un vistazo a más artículos de Sugerencia de Linux para consejos y tutoriales.