Consultar datos en una tabla temporal con control de versiones del sistema

Aplica a: SQL Server 2016 (13.x) y versiones posteriores Azure SQL DatabaseAzure SQL Managed InstanceBase de datos SQL en Microsoft Fabric

Para obtener el estado más reciente (actual) de los datos en una tabla temporal, consulta de la misma manera que una tabla no temporal. Si no se ocultan las columnas PERIOD, sus valores aparecen en una consulta SELECT *. Si especificas PERIOD columnas como HIDDEN, sus valores no aparecen en una SELECT * consulta. Cuando las PERIOD columnas estén ocultas, haz referencia específica en la SELECT cláusula para devolver sus valores.

Para realizar análisis basado en el tiempo, utiliza la FOR SYSTEM_TIME cláusula con cuatro subcláusulas específicas temporales para consultar datos a través de las tablas de corriente e historial. Para obtener más información sobre estas cláusulas, consulte tablas temporales y cláusula FROM con JOIN, APPLY y PIVOT

  • AS OF <date_time>
  • FROM <start_date_time> TO <end_date_time>
  • BETWEEN <start_date_time> AND <end_date_time>
  • CONTAINED IN (<start_date_time>, <end_date_time>)
  • ALL

Puedes especificar FOR SYSTEM_TIME de forma independiente para cada tabla en una consulta. Úselo dentro de expresiones de tabla común, funciones con valores de tabla y procedimientos almacenados. Cuando usas un alias de tabla con una tabla temporal, incluye la FOR SYSTEM_TIME cláusula entre el nombre de la tabla temporal y el alias ( véase Consulta para un tiempo específico usando el AS OF segundo ejemplo de la subcláusula ).

Consulta de una hora específica con la subcláusula AS OF

Utiliza la AS OF subcláusula para reconstruir el estado de los datos tal como estaban en un momento específico del pasado. Puedes reconstruir los datos con la precisión del tipo datetime2 que especificaste en PERIOD las definiciones de columnas.

Utiliza la AS OF subcláusula con literales o variables constantes para especificar dinámicamente la condición de tiempo. Los valores que proporcionas se interpretan como tiempo UTC.

Este primer ejemplo devuelve el estado de la dbo.Department tabla AS OF una fecha específica del pasado.

-- State of entire table AS OF specific date in the past
SELECT [DeptID],
       [DeptName],
       [ValidFrom],
       [ValidTo]
FROM [dbo].[Department] FOR SYSTEM_TIME
    AS OF '2021-09-01 T10:00:00.7230011';

Este segundo ejemplo compara los valores entre dos momentos dados de un subconjunto de filas.

DECLARE @ADayAgo AS DATETIME2;

SET @ADayAgo = DATEADD(DAY, -1, SYSUTCDATETIME());

-- Comparison between two points in time for subset of rows
SELECT D_1_Ago.DeptID,
       d.DeptID,
       D_1_Ago.DeptName,
       d.DeptName,
       D_1_Ago.ValidFrom,
       d.ValidFrom,
       D_1_Ago.ValidTo,
       d.ValidTo
FROM dbo.Department FOR SYSTEM_TIME
    AS OF @ADayAgo AS D_1_Ago
    INNER JOIN Department AS d
         ON D_1_Ago.DeptID = d.DeptID
        AND D_1_Ago.DeptID BETWEEN 1 AND 5;

Uso de vistas con la subcláusula AS OF en consultas temporales

Las vistas son útiles cuando se necesita un análisis complejo en un momento determinado. Un ejemplo común es generar hoy un informe empresarial con los valores del mes anterior.

Normalmente, los clientes tienen un modelo de bases de datos normalizado que incluye muchas tablas con relaciones de clave externa. Descubrir cómo se veían los datos de ese modelo normalizado en un momento del pasado puede ser complicado, porque todas las tablas cambian de forma independiente según su propia cadencia.

En este caso, la mejor opción consiste en crear una vista y aplicar la subcláusula AS OF a la vista completa. Este enfoque desacopla el modelado de la capa de acceso a datos del análisis en un momento en el tiempo, porque SQL Server aplica la AS OF cláusula de forma transparente a todas las tablas temporales que participan en la definición de la vista. Además, puede combinar tablas temporales y no temporales en la misma vista y AS OF se aplica solo a las temporales. Si la vista no hace referencia al menos a una tabla temporal, al aplicarle las cláusulas de consulta temporal se produce un error.

El siguiente código de ejemplo crea una vista que combina tres tablas temporales: Department, CompanyLocation y LocationDepartments:

CREATE VIEW [dbo].[vw_GetOrgChart]
AS
SELECT [CompanyLocation].LocID,
       [CompanyLocation].LocName,
       [CompanyLocation].City,
       [Department].DeptID,
       [Department].DeptName
FROM [dbo].[CompanyLocation]
    LEFT OUTER JOIN [dbo].[LocationDepartments]
        ON [CompanyLocation].LocID = LocationDepartments.LocID
    LEFT OUTER JOIN [dbo].[Department]
        ON LocationDepartments.DeptID = [Department].DeptID;
GO

Puede consultar la vista mediante la subclausa AS OF y un literal datetime2 :

/* Query view AS OF */
SELECT *
FROM [vw_GetOrgChart] FOR SYSTEM_TIME
    AS OF '2021-09-01 T10:00:00.7230011';

Consulta para realizar cambios en filas específicas a lo largo del tiempo

Las subcláusulas temporales FROM ... TO, BETWEEN ... AND y CONTAINED IN son útiles cuando necesite obtener todos los cambios históricos de una fila específica de la tabla actual (lo que también se conoce como una auditoría de datos).

Las dos primeras subcláusulas devolven versiones de fila que se solapan con un periodo especificado (es decir, aquellas que comenzaron antes del periodo dado y terminaron después de él), mientras que CONTAINED IN solo devolven aquellas que existían dentro de los límites del periodo especificado.

Si buscas solo versiones de filas no actuales, consulta directamente la tabla de historial para obtener el mejor rendimiento de consulta. Úsalo ALL para consultar datos actuales e históricos sin ninguna restricción.

/* Query using BETWEEN...AND sub-clause*/
SELECT [DeptID],
       [DeptName],
       [ValidFrom],
       [ValidTo],
       IIF (YEAR(ValidTo) = 9999, 1, 0) AS IsActual
FROM [dbo].[Department] FOR SYSTEM_TIME
    BETWEEN '2021-01-01' AND '2021-12-31'
WHERE DeptId = 1
ORDER BY ValidFrom DESC;

/* Query using CONTAINED IN sub-clause */
SELECT [DeptID],
       [DeptName],
       [ValidFrom],
       [ValidTo]
FROM [dbo].[Department] FOR SYSTEM_TIME
    CONTAINED IN ('2021-04-01', '2021-09-25')
WHERE DeptId = 1
ORDER BY ValidFrom DESC;

/* Query using ALL sub-clause */
SELECT [DeptID],
       [DeptName],
       [ValidFrom],
       [ValidTo],
       IIF (YEAR(ValidTo) = 9999, 1, 0) AS IsActual
FROM [dbo].[Department] FOR SYSTEM_TIME ALL
ORDER BY [DeptID], [ValidFrom] DESC;

Para resumir cómo se ven los datos a lo largo de un periodo, combina FOR SYSTEM_TIME BETWEEN ... AND con GROUP BY y agrega funciones. Utiliza esta técnica para estadísticas descriptivas y análisis de tendencias. Agrupa todas las versiones de fila que estuvieron activas durante la ventana en lugar de reconstruir un solo momento en el tiempo.

El siguiente ejemplo calcula estadísticas descriptivas para los salarios de los empleados a lo largo de una ventana temporal, agrupadas por departamento. Utiliza la Employee tabla versionada por sistema definida en tablas temporales y escenarios de uso de tablas temporales. La AnnualSalary columna es una medida numérica bien adecuada para las AVGfunciones , MIN, MAX, y STDEV .

La cláusula FOR SYSTEM_TIME BETWEEN ... AND devuelve todas las versiones de las filas que se superponen al período. Como resultado, los agregados incluyen todos los salarios que estuvieron vigentes durante la ventana. Cuando cambia el salario de un empleado, la consulta devuelve varias versiones para ese empleado, y cada versión contribuye a los agregados.

DECLARE @periodStart AS DATETIME2 = '2021-01-01';
DECLARE @periodEnd AS DATETIME2 = '2021-12-31';

SELECT [Department],
       AVG([AnnualSalary]) AS AvgSalary,
       MIN([AnnualSalary]) AS MinSalary,
       MAX([AnnualSalary]) AS MaxSalary,
       STDEV([AnnualSalary]) AS SalaryStdDev,
       COUNT(*) AS RowVersions
FROM [dbo].[Employee] FOR SYSTEM_TIME
    BETWEEN @periodStart AND @periodEnd
GROUP BY [Department]
ORDER BY AvgSalary DESC;

Contenido relacionado

  • Last updated on