Tablas temporales

Se aplica a: síSQL Server 2016 (13.x) y versiones posteriores SíAzure SQL Database SíInstancia administrada de Azure SQL

En SQL Server 2016 se presentó la compatibilidad con las tablas temporales (también denominadas tablas temporales con versión del sistema) como función de base de datos que incorpora compatibilidad integrada para proporcionar información sobre los datos almacenados en la tabla en cualquier momento, en vez de solo los datos que son correctos en la actualidad. La característica temporal de las bases de datos surgió con ANSI SQL 2011.

Guía de inicio rápido

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

Una tabla temporal con versiones del sistema es un tipo de tabla de usuario pensada para conservar un historial completo de los cambios de datos y para facilitar los análisis en un momento específico. Este tipo de tabla temporal se conoce como tabla temporal con versión del sistema, porque el período de validez de cada fila se administra por medio del sistema (es decir, del 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 periodo. 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.

Aparte de estas columnas, una tabla temporal contiene también una referencia a otra tabla con un esquema reflejado. El sistema usa esta tabla para almacenar automáticamente la versión anterior de una fila de la tabla temporal cada vez que dicha fila se actualiza o elimina. Esta tabla adicional se conoce como tabla de historial, mientras que la tabla principal que almacena las versiones de fila actuales (reales) se conoce como tabla actual o, simplemente, como tabla temporal. Durante la creación de una tabla temporal, los usuarios pueden especificar una tabla de historial existente (debe admitir esquemas) o dejar que el sistema cree una tabla de historial 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 las dos columnas datetime2 adicionales siguientes para definir el período 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 SysStartTime.
  • Columna de fin del período: El sistema registra la hora de fin de la fila en esta columna, que normalmente se denomina SysEndTime.

La tabla actual contiene el valor actual de cada fila. La tabla de historial contiene cada valor 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 este sencillo ejemplo se ilustra un escenario con información sobre empleados en una hipotética base de datos de recursos humanos:

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));
  • Instrucciones INSERT: en una instrucción INSERT, el sistema establece el valor de la columna SysStartTime (en el ejemplo, se le llama 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 SysEndTime (en el ejemplo, se le llama ValidTo) en el valor máximo de 31-12-9999. Esto marca la fila como abierta.
  • Instrucciones UPDATE: en una instrucción UPDATE, el sistema almacena el valor anterior de la fila en la tabla de historial y establece el valor de la columna SysEndTime 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 SysStartTime 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 SysEndTime sigue siendo el valor máximo de 9999-12-31.
  • Operaciones DELETE: en una operación DELETE, el sistema almacena el valor anterior de la fila en la tabla de historial y establece el valor de la columna SysEndTime 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.
  • Instrucción MERGE: En una instrucción 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 <table> 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 las tablas actuales 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.

Diagrama que muestra el funcionamiento de las consultas temporales.

La siguiente consulta busca versiones de fila para la fila Employee con EmployeeID = 1000 que estaban activas al menos durante una parte del período comprendido entre el 1 de enero de 2014 y el 1 de enero de 2015 (incluido el límite superior):

SELECT * FROM Employee
  FOR SYSTEM_TIME
    BETWEEN '2014-01-01 00:00:00.0000000' AND '2015-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 (SysStartTime = SysEndTime). Estas filas se generarán si realiza varias actualizaciones en la misma clave principal en la misma transacción. En tal caso, las consultas temporales muestran solo las versiones de fila antes de las transacciones y las que pasaron a ser reales después de las transacciones. Si necesita incluir esas filas en el análisis, consulte directamente la tabla de historial.

En la siguiente tabla, SysStartTime en la columna Filas certificadas representa el valor reflejado en la columna SysStartTime de la tabla que se está consultando y SysEndTime , el valor reflejado en la columna SysEndTime de la tabla que se está consultando. Para obtener la sintaxis completa y ejemplos, vea FROM (Transact-SQL) y Consulta de los datos de una tabla temporal con control de versiones del sistema.

Expression Filas certificadas Descripción
AS OF<date_time> SysStartTime <= date_time AND SysEndTime > date_time Devuelve una tabla con filas que contienen los valores que fueron reales (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 <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> SysStartTime < end_date_time AND SysEndTime > 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 ser activas antes del valor del parámetro <start_date_time> en el argumento FROM o si dejaron de serlo después del valor del parámetro <end_date_time> en el 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 ser activas justamente en el límite inferior definido por el punto de conexión FROM ni tampoco aquellas 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> SysStartTime <= end_date_time AND SysEndTime > start_date_time Igual que la descripción anterior de FOR SYSTEM_TIME FROM <start_date_time>TO <end_date_time>, salvo que la tabla de filas devuelta 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>) SysStartTime >= start_date_time AND SysEndTime <= end_date_time Devuelve una tabla con los valores de todas las versiones de fila que se abrieron y cerraron dentro del rango de tiempo especificado definido por los dos valores de fecha y hora 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.

Nota

Si quiere, puede ocultar estas columnas de periodo, de forma que las consultas que no hagan referencia explícitamente a estas columnas de periodo no devuelvan esas columnas (el escenario de SELECT * FROM <table> ). Para devolver una columna oculta, basta con hacer referencia explícita a dicha 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 obtener más información sobre cómo usar la cláusula HIDDEN , vea CREATE TABLE (Transact-SQL) y ALTER TABLE (Transact-SQL).

Pasos siguientes