Tablas temporales

  • Artículo

Se aplica a: SQL Server 2016 (13.x) y versiones posteriores Azure SQL Database Azure SQL Managed Instance

Las tablas temporales (también conocidas como tablas temporales con control de versiones del sistema) son una característica de base de datos que ofrece soporte integrado para proporcionar información sobre los datos almacenados en la tabla en cualquier momento en el tiempo, en vez de únicamente los datos que son correctos en el momento actual determinado.

Puede ver Introducción a las tablas temporales con versión del sistema y revisar Escenarios de uso de tablas temporales.

¿Qué es una tabla temporal con versión del sistema?

Una tabla temporal con versión del sistema es un tipo de tabla de usuario diseñada para conservar un historial completo de los cambios de datos y facilitar los análisis en un momento específico. Este tipo de tabla temporal se conoce como tabla temporal con control de versiones del sistema, porque el periodo de validez de cada fila lo administra el sistema (es decir, el motor de base de datos).

Cada tabla temporal tiene dos columnas definidas explícitamente, cada una con un tipo de datos datetime2 . Estas columnas se conocen como columnas de período. Estas columnas de periodo son de uso exclusivo por parte del sistema para registrar el período de validez de cada fila cada vez que una fila se modifica. La tabla principal en la que se almacenan los datos actuales se conoce como tabla actual o, simplemente, como tabla temporal.

Además de estas columnas de periodo, una tabla temporal contiene también una referencia a otra tabla con un esquema reflejado, denominada tabla de historial. El sistema usa la tabla de historial para almacenar automáticamente la versión anterior de una fila de la tabla temporal cada vez que esa fila se actualiza o elimina. Durante la creación de tablas temporales, puede especificar una tabla de historial existente (que debe admitir el esquema) o dejar que el sistema cree una predeterminada.

¿Por qué temporal?

Los orígenes de datos reales son dinámicos y, con más frecuencia que las decisiones no empresariales, se basan en la información que los analistas obtienen de la evolución de los datos. Estos son los casos de uso de tablas temporales:

  • Realizar una auditoría de todos los cambios de datos y realizar análisis forenses de datos cuando sea necesario
  • Reconstruir el estado de los datos a partir de cualquier momento en el pasado
  • Calcular las tendencias en el tiempo
  • Mantener una dimensión de variación lenta para aplicaciones de apoyo de decisiones
  • Recuperarse de cambios accidentales de datos y errores de aplicación

¿Cómo funciona la característica temporal?

La creación de versiones del sistema para una tabla se implementa como un par de tablas: una tabla actual y una tabla de historial. Dentro de cada una de estas tablas, se usan dos columnas datetime2 adicionales para definir el periodo de validez de cada fila:

  • Columna de inicio del periodo: el sistema registra la hora de inicio de la fila en esta columna, que normalmente se denomina ValidFrom.

  • Columna de fin del periodo: el sistema registra la hora de fin de la fila en esta columna, que normalmente se denomina ValidTo.

La tabla actual contiene el valor actual de cada fila. La tabla de historial contiene cada valor previo (la versión anterior) de cada fila, si existe, y las horas de inicio y fin del periodo de validez.

Diagrama que muestra el funcionamiento de una tabla temporal.

En el script siguiente se muestra un escenario con información de empleados:

CREATE TABLE dbo.Employee (
    [EmployeeID] INT NOT NULL PRIMARY KEY CLUSTERED,
    [Name] NVARCHAR(100) NOT NULL,
    [Position] VARCHAR(100) NOT NULL,
    [Department] VARCHAR(100) NOT NULL,
    [Address] NVARCHAR(1024) NOT NULL,
    [AnnualSalary] DECIMAL(10, 2) NOT NULL,
    [ValidFrom] DATETIME2 GENERATED ALWAYS AS ROW START,
    [ValidTo] DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

Para más información, vea Creación de una tabla temporal con versiones del sistema.

  • Inserta: el sistema establece el valor de la columna ValidFrom en la hora de inicio de la transacción actual (en la zona horaria UTC) según el reloj del sistema y asigna el valor de la columna ValidTo al valor máximo de 9999-12-31. Esto marca la fila como abierta.

  • Actualiza: el sistema almacena el valor anterior del registro en la tabla de historial y establece el valor de la columna ValidTo en la hora de inicio de la transacción actual (en la zona horaria UTC) según el reloj del sistema. Esto marca la fila como cerrada, con un periodo registrado durante el que la fila fue válida. En la tabla actual, la fila se actualiza con su nuevo valor y el sistema establece el valor de la columna ValidFrom en la hora de inicio de la transacción actual (en la zona horaria UTC) según el reloj del sistema. El valor de la fila actualizada en la tabla actual para la columna ValidTo sigue siendo el valor máximo de 9999-12-31.

  • Elimina: el sistema almacena el valor anterior del registro en la tabla de historial y establece el valor de la columna ValidTo en la hora de inicio de la transacción actual (en la zona horaria UTC) según el reloj del sistema. Esto marca la fila como cerrada, con un periodo registrado durante el que la fila anterior fue válida. En la tabla actual, la fila se quita. Las consultas de la tabla actual no devuelven esa fila. Solo las consultas que tengan que ver con los datos de historial devolverán datos relativos a una fila cerrada.

  • Combinar: la operación se comporta exactamente igual que si se ejecutaran hasta tres instrucciones (INSERT,UPDATE, y/o DELETE), en función de lo que se haya especificado como acción en la instrucción MERGE.

Las horas registradas en las columnas datetime2 del sistema se basan en la hora de inicio de la propia transacción. Por ejemplo, todas las filas insertadas en una única transacción tienen la misma hora UTC registrada en la columna correspondiente al inicio del período SYSTEM_TIME.

Al ejecutar consultas de modificación de datos en una tabla temporal, el Motor de base de datos agrega una fila a la tabla de historial aunque no cambie ningún valor de columna.

¿Cómo se consultan los datos temporales?

La instrucción SELECT ... FROM <table> tiene una nueva cláusula FOR SYSTEM_TIME, con cinco subcláusulas temporales específicas para consultar datos de las tablas actual y de historial. Esta nueva sintaxis de la instrucción SELECT se puede usar directamente en una sola tabla, propagarse por varias combinaciones y por las vistas de varias tablas temporales.

Cuando se realiza una consulta con la cláusula FOR SYSTEM_TIME mediante una de las cinco subclases, se incluyen datos históricos de la tabla temporal, como se muestra en la imagen siguiente.

Diagrama que muestra el funcionamiento de las consultas temporales.

La consulta siguiente busca versiones de fila para un empleado con la condición de filtro WHERE EmployeeID = 1000 que hayan estado activas al menos durante una parte del período comprendido entre el 1 de enero de 2021 y el 1 de enero de 2022 (incluido el límite superior):

SELECT * FROM Employee
    FOR SYSTEM_TIME
        BETWEEN '2021-01-01 00:00:00.0000000' AND '2022-01-01 00:00:00.0000000'
            WHERE EmployeeID = 1000 ORDER BY ValidFrom;

FOR SYSTEM_TIME filtra las filas que tienen un período de validez con una duración cero (ValidFrom = ValidTo).

Estas filas se generan si realiza varias actualizaciones en la misma clave principal en la misma transacción. En ese caso, las consultas temporales solo devuelven las versiones de fila antes de las transacciones y las filas actuales después de las transacciones.

Si necesita incluir esas filas en el análisis, consulte directamente la tabla de historial.

En la siguiente tabla, ValidFrom en la columna Filas certificadas representa el valor de la columna ValidFrom de la tabla que se consulta y ValidTo el valor de la columna ValidTo de la tabla que se consulta. Para obtener la sintaxis completa y ejemplos, vea Cláusula FROM más JOIN, APPLY, PIVOT y Consulta de datos en una tabla temporal con control de versiones del sistema.

Expression Filas certificadas Nota:
AS OF date_time ValidFrom <= date_time AND ValidTo > date_time Devuelve una tabla con filas que contienen los valores que eran actuales en el momento determinado especificado en el pasado. Internamente, se realiza una unión entre la tabla temporal y su tabla de historial. Los resultados se filtran para devolver los valores de la fila que era válida en el momento determinado especificado por el parámetro date_time. El valor de una fila se considera válido si el valor de system_start_time_column_name es menor o igual que el valor del parámetro date_time y el valor de system_end_time_column_name es mayor que el valor del parámetro date_time.
FROM start_date_time TO end_date_time ValidFrom < end_date_time AND ValidTo > start_date_time Devuelve una tabla con los valores de todas las versiones de fila que estaban activas dentro del rango de tiempo especificado, independientemente de si empezaron a estar activas antes del valor del parámetro start_date_time del argumento FROM o si dejaron de estarlo después del valor del parámetro end_date_time del argumento TO. Internamente, se realiza una unión entre la tabla temporal y su tabla de historial. Los resultados se filtran para devolver los valores de todas las versiones de fila que estaban activas en cualquier momento dentro del intervalo de tiempo especificado. No se incluyen las filas que han dejado de estar activas justamente en el límite inferior definido por el punto de conexión FROM, ni tampoco las que se han activado exactamente en el límite superior definido por el punto de conexión TO.
BETWEEN start_date_time AND end_date_time ValidFrom <= end_date_time AND ValidTo > start_date_time Igual que la descripción anterior de FOR SYSTEM_TIME FROM start_date_time TO end_date_time, salvo que incluye las filas que se activaron en el límite superior definido por el punto de conexión end_date_time.
CONTAINED IN (start_date_time, end_date_time) ValidFrom >= start_date_time AND ValidTo <= end_date_time Devuelve una tabla con los valores de todas las versiones de fila que se han abierto y cerrado dentro del rango de tiempo especificado definido por los dos valores de periodo en el argumento CONTAINED IN. Se incluyen las filas que se activaron justamente en el límite inferior o que dejaron de estarlo exactamente en el límite superior.
ALL Todas las filas Devuelve la unión de las filas pertenecientes a la tabla actual y a la tabla de historial.

Ocultación de las columnas de periodo

Puede optar por ocultar las columnas de periodo, de modo que las consultas que no hacen referencia explícitamente a ellas no las devuelvan (por ejemplo, al ejecutar SELECT * FROM <table>).

Para devolver una columna oculta, debe hacer referencia explícita a esa columna en la consulta. Del mismo modo, las instrucciones INSERT y BULK INSERT continuarán como si estas nuevas columnas de periodo no estuvieran presentes (y los valores de columna se rellenan automáticamente).

Para obtener más información sobre cómo usar la cláusula HIDDEN, consulte CREATE TABLE y ALTER TABLE.

Ejemplos

Contenido relacionado