Compartir por

Consulta de los datos de una tabla temporal con control de versiones del sistema

  • Artigo

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

Cuando quiera obtener el estado más reciente de los datos (el actual) de una tabla temporal, puede realizar la consulta de la misma manera que en una tabla no temporal. Si no se ocultan las columnas PERIOD, sus valores aparecen en una consulta SELECT *. Si ha especificado las columnas PERIOD como HIDDEN, sus valores no aparecen en una consulta SELECT *. Cuando las columnas PERIOD están ocultas, debe hacer referencia a las columnas PERIOD de forma concreta en la cláusula SELECT para devolver los valores de estas columnas.

Para realizar cualquier tipo de análisis basado en tiempo, use la nueva cláusula FOR SYSTEM_TIME con cuatro subcláusulas temporales específicas para consultar datos en las tablas actual y de historial. Para obtener más información sobre estas cláusulas, vea Tablas temporales y cláusula FROM más JOIN, APPLY, 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

FOR SYSTEM_TIME puede especificarse de forma independiente para cada tabla de una consulta. Se puede usar dentro de expresiones comunes de tabla, de funciones con valores de tabla y de procedimientos almacenados. Cuando se usa un alias de tabla con tablas temporales, la cláusula FOR SYSTEM_TIME se debe incluir entre el nombre de la tabla temporal y el alias; (vea el segundo ejemplo de Consulta de una hora específica con la subcláusula AS OF a continuación).

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

Use la subcláusula AS OF cuando necesite reconstruir el estado de datos tal y como se encontraba en una hora específica del pasado. Puede reconstruir los datos con la precisión del tipo datetime2 que se ha especificado en las definiciones de columnas PERIOD.

La subcláusula AS OF se puede usar con literales constantes o con variables para que pueda especificar de forma dinámica una condición temporal. Los valores proporcionados se interpretan como hora UTC.

Este primer ejemplo devuelve el estado de la tabla dbo.Department AS OF en 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 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

El uso de vistas es útil en escenarios en los que se necesita un análisis complejo de un momento dado. Un ejemplo común es generar un informe empresarial hoy 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. Determinar qué aspecto tenían los datos de ese modelo normalizado en un momento del pasado puede ser complicado, ya que todas las tablas cambian de forma independiente, a su ritmo.

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 permite desacoplar el modelado de la capa de acceso a los datos del análisis de un momento dado ya que SQL Server se aplica a la cláusula AS OF de forma transparente a todas las tablas temporales que participen 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 JOIN [dbo].[LocationDepartments]
    ON [CompanyLocation].LocID = LocationDepartments.LocID
LEFT JOIN [dbo].[Department]
    ON LocationDepartments.DeptID = [Department].DeptID;
GO

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

/* Querying 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 es necesario obtener todos los cambios históricos de una fila específica en la tabla actual (lo que también se conoce como auditoría de datos).

Las dos primeras subcláusulas devuelven versiones de fila que se superponen en un periodo específico (es decir, las que se han iniciado antes del periodo dado y han terminado después de este), mientras que CONTAINED IN devuelve solo las que han existido dentro de los límites del periodo especificado.

Si busca versiones de fila no actuales, debe consultar la tabla de historial directamente para un mejor rendimiento de la consulta. Use ALL cuando necesite consultar datos históricos y actuales sin restricciones.

/* 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;

Contenido relacionado