Tablas temporales

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 versión 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 versiones del sistema, porque el período 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, los usuarios pueden 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 versión del sistema de 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 período: el sistema registra la hora de inicio de la fila en esta columna, que normalmente se denomina ValidFrom.
  • Columna de fin del período: 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 en el que se 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.

  • INSERTS: 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 31-12-9999. Esto marca la fila como abierta.
  • UPDATES: 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.
  • DELETES: 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 devolverán esa fila. Solo las consultas que tengan que ver con los datos de historial devolverán datos relativos a una fila cerrada.
  • MERGE: 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.

Importante

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 tendrán la misma hora UTC registrada en la columna correspondiente al inicio del período SYSTEM_TIME .

¿Cómo se consultan los datos temporales?

La cláusula FROM<tabla> de la instrucción SELECT tiene una nueva cláusula FOR SYSTEM_TIME con cinco subcláusulas temporales específicas con las que se pueden consultar datos de la tabla actual y las tablas históricas. 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 incluirán datos históricos de la tabla temporal, como se muestra en la imagen siguiente.

Diagrama en el que se 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;

Nota

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

Estas filas se generarán 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 los ejemplos, vea FROM (Transact-SQL) y Consulta de datos en una tabla temporal con versión del sistema.

Expression Filas certificadas Nota
AS OFdate_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, y los resultados se filtran para devolver los valores de la fila que era válida en el momento determinado especificado por el parámetro fecha_y_hora. 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 fecha_y_hora y el valor de system_end_time_column_name es mayor que el valor del parámetro fecha_y_hora.
FROMfecha_y_hora_de_inicioTOfecha_y_hora_de_finalización ValidFrom<fecha_y_hora_de_inicio AND ValidTo>fecha_y_hora_de_finalización 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 fecha_y_hora_de_inicio del argumento FROM o si dejaron de estarlo después del valor del parámetro fecha_y_hora_de_finalización del argumento TO. Internamente, se realiza una unión entre la tabla temporal y su tabla de historial y 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.
BETWEENstart_date_timeANDfecha_y_hora_de_finalización ValidFrom<= end_date_time AND ValidTo>fecha_y_hora_de_finalización Igual que la descripción anterior de FOR SYSTEM_TIME FROMfecha_y_hora_de_inicioTOend_date_time, salvo que la tabla de filas devuelta incluya las filas que se han activado en el límite superior definido por el punto de conexión fecha_y_hora_de_finalización.
CONTAINED IN (fecha_y_hora_de_inicio, fecha_y_hora_de_finalización) ValidFrom>= fecha_y_hora_de_inicio AND ValidTo<= fecha_y_hora_de_finalización 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 rellenarán automáticamente).

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

Ejemplos

ASP.NET

Vea esta aplicación web de ASP.NET Core para aprender a compilar una aplicación temporal mediante tablas temporales.

Descarga de la base de datos de ejemplo Adventure Works

Puede descargar la base de datos AdventureWorks para SQL Server, que incluye características de tabla temporal.

Consulte también

Pasos siguientes