Microsoft SQL Server
Tablas temporales

Observaciones

Crear tablas temporales

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 (2) GENERATED ALWAYS AS ROW START  
  , [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END  
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)  
 )    
 WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));  

INSERTOS: en un INSERT , el sistema establece el valor de la columna ValidFrom a 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.

ACTUALIZACIONES: en una ACTUALIZACIÓN , el sistema almacena el valor anterior de la fila en la tabla de historial y establece el valor de la columna ValidTo en el momento 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 período registrado para el cual la fila era válida. En la tabla actual, la fila se actualiza con su nuevo valor y el sistema establece el valor de la columna ValidFrom a la hora de inicio de la transacción (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.

ELIMINAR : en BORRAR , el sistema almacena el valor anterior de la fila en la tabla de historial y establece el valor de la columna ValidTo en el momento 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 período registrado para el cual la fila anterior era válida. En la tabla actual, la fila se elimina. Las consultas de la tabla actual no devolverán esta fila. Solo las consultas que tratan con datos históricos devuelven datos para los que se cierra una fila.

MERGE : en un MERGE , la operación se comporta exactamente como si se ejecutaran hasta tres sentencias (un INSERT , un UPDATE y / o un DELETE ), según lo que se especifique como acciones en la sentencia MERGE .

Consejo: Los tiempos registrados en las columnas datetime2 del sistema se basan en la hora de inicio de la transacción. Por ejemplo, todas las filas insertadas dentro de una sola transacción tendrán la misma hora UTC registrada en la columna correspondiente al inicio del período SYSTEM_TIME .

¿Cómo puedo consultar los datos temporales?

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;  

Devuelve el valor real especificado en el tiempo (FOR SYSTEM_TIME AS OF )

Devuelve una tabla con una fila que contiene los valores que eran reales (actuales) en el momento especificado en el pasado.

SELECT * FROM Employee   
    FOR SYSTEM_TIME AS  OF '2016-08-06 08:32:37.91'

PARA SYSTEM_TIME ENTRE Y

Igual que arriba en la descripción de FOR SYSTEM_TIME FROM <start_date_time> TO <end_date_time>, excepto que la tabla de filas devueltas incluye filas que se activaron en el límite superior definido por el punto final <end_date_time>.

SELECT * FROM Employee   
    FOR SYSTEM_TIME BETWEEN  '2015-01-01' AND '2015-12-31' 

PARA SYSTEM_TIME FROM A

Devuelve una tabla con los valores para todas las versiones de fila que estaban activas dentro del rango de tiempo especificado, independientemente de si comenzaron a estar activas antes del valor del parámetro <start_date_time> para el argumento FROM o dejaron de estar activas después del valor del parámetro <end_date_time> para el A argumento. 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 durante el rango de tiempo especificado. Las filas que se activaron exactamente en el límite inferior definido por el punto final FROM se incluyen y los registros que se activaron exactamente en el límite superior definido por el punto final TO no se incluyen.

SELECT * FROM Employee   
    FOR SYSTEM_TIME FROM '2015-01-01' TO '2015-12-31' 

PARA SYSTEM_TIME CONTENIDO EN ( , )

Devuelve una tabla con los valores para 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 para el argumento CONTAINED IN. Se incluyen las filas que se activaron exactamente en el límite inferior o dejaron de estar activas exactamente en el límite superior.

SELECT * FROM Employee
    FOR SYSTEM_TIME CONTAINED IN ('2015-04-01', '2015-09-25')   

PARA SYSTEM_TIME ALL

Devuelve la unión de filas que pertenecen a la tabla actual y la tabla de historial.

SELECT * FROM Employee
    FOR SYSTEM_TIME ALL

Creación de una tabla temporal con versión de sistema optimizada para la memoria y limpieza de la tabla de historial de SQL Server

Crear una tabla temporal con una tabla de historial predeterminada es una opción conveniente cuando desea controlar los nombres y aún depende del sistema para crear la tabla de historial con la configuración predeterminada. En el siguiente ejemplo, una nueva tabla temporal optimizada con memoria del sistema vinculada a una nueva tabla de historial basada en disco.

CREATE SCHEMA History  
GO  
CREATE TABLE dbo.Department   
(  
    DepartmentNumber char(10) NOT NULL PRIMARY KEY NONCLUSTERED,   
    DepartmentName varchar(50) NOT NULL,   
    ManagerID int  NULL,   
    ParentDepartmentNumber char(10) NULL,   
    SysStartTime datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,   
    SysEndTime datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,     
    PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)     
)  
WITH   
    (  
        MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA,  
        SYSTEM_VERSIONING = ON ( HISTORY_TABLE = History.DepartmentHistory )   
    );  

Limpiando la tabla de historial de SQL Server Con el tiempo, la tabla de historial puede crecer significativamente. Como no se permite insertar, actualizar o eliminar datos de la tabla de historial, la única forma de limpiar la tabla de historial es deshabilitar primero la versión del sistema:

ALTER TABLE dbo.Employee

SET (SYSTEM_VERSIONING = OFF); IR

Eliminar datos innecesarios de la tabla de historial:

    DELETE FROM dbo.EmployeeHistory

WHERE EndTime <= '2017-01-26 14:00:29';

y luego volver a habilitar el control de versiones del sistema:

ALTER TABLE dbo.Employee

SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo]. [EmployeeHistory], DATA_CONSISTENCY_CHECK = ON));

La limpieza de la tabla de historial en las bases de datos de Azure SQL es un poco diferente, ya que las bases de datos de Azure SQL tienen soporte incorporado para la limpieza de la tabla de historial. Primero, la limpieza de la retención del historial temporal debe habilitarse en un nivel de base de datos:

ALTER DATABASE CURRENT

CONFIGURAR TEMPORAL_HISTORIAL_RETENCIÓN EN IR

A continuación, establezca el período de retención por tabla:

ALTER TABLE dbo.Employee

SET (SYSTEM_VERSIONING = ON (HISTORY_RETENTION_PERIOD = 90 DAYS));

Esto eliminará todos los datos en la tabla de historial con más de 90 días. Las bases de datos locales de SQL Server 2016 no son compatibles con TEMPORAL_HISTORY_RETENTION y HISTORY_RETENTION_PERIOD y cualquiera de las dos consultas anteriores se ejecutan en las bases de datos locales de SQL Server 2016, se producirán los siguientes errores.

Para TEMPORAL_HISTORY_RETENTION el error será:

Msg 102, Level 15, State 6, Line 34

Sintaxis incorrecta cerca de 'TEMPORAL_HISTORY_RETENTION'.

Para HISTORY_RETENTION_PERIOD el error será:

Msg 102, Level 15, State 1, Line 39

Sintaxis incorrecta cerca de 'HISTORY_RETENTION_PERIOD'.