Consulta de datos en una tabla temporal con versión del sistema

  • Artículo

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 las PERIOD columnas no están ocultas, sus valores aparecen en una SELECT * consulta. Si especificó PERIOD columnas como HIDDEN, sus valores no aparecen en una SELECT * consulta. 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 más información sobre estas cláusulas, consulte Tablas temporales y FROM (Transact-SQL).

  • 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 en 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 una tabla temporal, la FOR SYSTEM_TIME cláusula debe incluirse entre el nombre de la tabla temporal y el alias (consulte Consulta de una hora específica mediante el AS OF segundo ejemplo de subclause ).

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 AS OF subclausa se puede usar con literales constantes o con variables, de modo que pueda especificar dinámicamente la condición de tiempo. 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 AS OF subclausa 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. El uso de este enfoque permite desacoplar el modelado de la capa de acceso a datos desde el análisis a un momento dado, ya que SQL Server aplica AS OF la cláusula de forma transparente a todas las tablas temporales que participan en la definición de vista. Además, puede combinar las tablas temporales con tablas no temporales en la misma vista y AS OF solo se aplica a las temporales. Si la vista no hace referencia al menos a una tabla temporal, se produce un error al aplicar cláusulas de consulta temporales a ella.

El código de ejemplo siguiente crea una vista que combina tres tablas temporales: Department, CompanyLocationy 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

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

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

Como alternativa, puede consultar la vista mediante la AS OF subclausa con una zona horaria local y AT TIME ZONE:

/* Querying view AS OF with local time*/
DECLARE @LocalTime DATETIMEOFFSET = '2021-09-01 10:00:00.7230011 -07:00';

SELECT * FROM [vw_GetOrgChart]
FOR SYSTEM_TIME AS OF @LocalTime AT TIME ZONE 'UTC';

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 solo busca versiones de fila no actuales, debe consultar directamente la tabla de historial para obtener el mejor rendimiento de las consultas. 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;

Pasos siguientes