Escenarios de uso de tablas temporalesTemporal Table Usage Scenarios

SE APLICA A: síSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noAlmacenamiento de datos paralelos APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

Las tablas temporales suelen ser útiles en escenarios que requieren historial de seguimiento de cambios de datos.Temporal Tables are generally useful in scenarios that require tracking history of data changes.
Debido a las enormes ventajas de productividad, se recomienda tener en cuenta las tablas temporales en los siguientes casos de uso.We recommend you to consider Temporal Tables in the following use cases for major productivity benefits.

Auditoría de datosData Audit

Use el control de versiones del sistema temporal en tablas que almacenan información crítica para la que necesita realizar un seguimiento de qué ha cambiado y cuándo, y para realizar análisis forense de datos en cualquier momento.Use temporal system-versioning on tables that store critical information for which you need to keep track of what has changed and when, and to perform data forensics at any point in time.
Las tablas temporales con versión del sistema permiten planear escenarios de auditoría de datos en las primeras fases del ciclo de desarrollo o agregar auditoría de datos a aplicaciones o soluciones existentes cuando se necesita.Temporal system-versioned tables allows you to plan for data audit scenarios in the early stages of the development cycle or to add data auditing to existing applications or solutions when you need it.

El siguiente diagrama muestra a un escenario de una tabla Employee con una muestra de datos que incluye versiones de fila actuales (marcadas en color azul) e históricas (marcadas en color gris).The following diagram shows an Employee table scenario with the data sample including current (marked with blue color) and historical row versions (marked with grey color).
La parte derecha del diagrama visualiza las versiones de fila en el eje de tiempo y cuáles son las filas seleccionadas con diferentes tipos de consulta en una tabla temporal con o sin la cláusula SYSTEM_TIME.The right-hand portion of the diagram visualizes row versions on time axis and what are the rows you select with different types of querying on temporal table with or without SYSTEM_TIME clause.

EscenarioDeUsoTemporal1TemporalUsageScenario1

Habilitación del control de versiones del sistema en una nueva tabla para auditoría de datosEnabling system-versioning on a new table for data audit

Si ha identificado información que necesita datos de auditoría, cree tablas de base de datos como temporales con versión del sistema.If you have identified information that needs data audit, create database tables as temporal system-versioned. En este sencillo ejemplo se ilustra un escenario con información sobre empleados en una hipotética base de datos de recursos humanos:The following simple example illustrates a scenario with Employee information in hypothetical HR database:

CREATE TABLE 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));  

En Creación de una tabla temporal con control de versiones del sistemase describen diversas opciones para crear una tabla temporal con versión del sistema.Various options to create temporal system-versioned table are described in Creating a System-Versioned Temporal Table.

Habilitación del control de versiones del sistema en una tabla existente para auditoría de datosEnabling system-versioning on an existing table for data audit

Si necesita realizar auditoría de datos en bases de datos existentes, utilice ALTER TABLE para extender las tablas no temporales para convertirlas en tablas con versión del sistema.If you need to perform data audit in existing databases, use ALTER TABLE to extend non-temporal tables to become system-versioned. Para evitar cambios importantes en la aplicación, agregue las columnas de período como HIDDEN, como se explica en Alter Non-Temporal Table to be System-Versioned Temporal Table (Modificación de una tabla no temporal para convertirla en tabla temporal con versión del sistema).In order to avoid breaking changes in your application, add period columns as HIDDEN, as explained in Alter Non-Temporal Table to be System-Versioned Temporal Table. En el ejemplo siguiente se muestra cómo habilitar el control de versiones del sistema en una tabla de Employee existente en una hipotética base de datos de recursos humanos:The following example illustrates enabling system-versioning on an existing Employee table in a hypothetical HR database:

/*   
Turn ON system versioning in Employee table in two steps   
(1) add new period columns (HIDDEN)   
(2) create default history table   
*/   
ALTER TABLE Employee   
ADD   
    ValidFrom datetime2 (2) GENERATED ALWAYS AS ROW START HIDDEN    
        constraint DF_ValidFrom DEFAULT DATEADD(second, -1, SYSUTCDATETIME())  
    , ValidTo datetime2 (2)  GENERATED ALWAYS AS ROW END HIDDEN     
        constraint DF_ValidTo DEFAULT '9999.12.31 23:59:59.99'  
    , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);   
  
ALTER TABLE Employee    
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Employee_History));  

Después de ejecutar el script anterior, todos los cambios de datos se recopilarán de forma transparente en la tabla de historial.After executing the above script, all data changes will be collected transparently in the history table.
En un escenario de auditoría de datos típico, consultaría todos los cambios que se aplicaron a una fila individual en un período de tiempo de interés.In typical data audit scenario, you would query for all data changes that were applied to an individual row within a period of time of interest. La tabla de historial predeterminada se crea con el árbol B de almacén de filas agrupado para tratar eficazmente este caso de uso.The default history table is created with clustered row-store B-Tree to efficiently address this use case.

Realización de análisis de datosPerforming data analysis

Después de habilitar el control de versiones mediante cualquier de los enfoques anteriores, basta una consulta para realizar la auditoría de datos.After enabling system-versioning using either of the above approaches, data audit is just one query away from you. La siguiente consulta busca versiones de fila para el registro 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):The following query searches for row versions for Employee record with EmployeeID = 1000 that were active at least for a portion of period between 1st January of 2014 and 1st January 2015 (including the upper boundary):

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;  

Reemplace FOR SYSTEM_TIME BETWEEN...AND por FOR SYSTEM_TIME ALL para analizar todo el historial de cambios de datos para ese empleado concreto:Replace FOR SYSTEM_TIME BETWEEN...AND with FOR SYSTEM_TIME ALL to analyze the entire history of data changes for that particular employee:

SELECT * FROM Employee   
    FOR SYSTEM_TIME ALL WHERE    
        EmployeeID = 1000 ORDER BY ValidFrom;  

Para buscar versiones de fila que estaban activas solo dentro de un período (y no fuera de él), use CONTAINED IN.To search for row versions that were active only within a period (and not outside of it), use CONTAINED IN. Esta consulta es muy eficaz porque solo consulta la tabla de historial:This query is very efficient because it only queries the history table:

SELECT * FROM Employee FOR SYSTEM_TIME    
    CONTAINED IN ('2014-01-01 00:00:00.0000000', '2015-01-01 00:00:00.0000000')   
        WHERE EmployeeID = 1000 ORDER BY ValidFrom;  

Por último, en algunos escenarios de auditoría, puede que desee ver el aspecto de toda la tabla en un momento dado en el pasado:Finally, in some audit scenarios, you may want to see how entire table looked like at any point in time in the past:

SELECT * FROM Employee FOR SYSTEM_TIME AS OF '2014-01-01 00:00:00.0000000' ;  

Las tablas temporales con versión del sistema almacenan valores para columnas de período en la zona horaria UTC, aunque siempre es más conveniente trabajar con la zona horaria local tanto para filtrar datos como para mostrar resultados.System-versioned temporal tables store values for period columns in UTC time zone, while it is always more convenient to work with local time zone both for filtering data and displaying results. En el ejemplo de código siguiente se muestra cómo aplicar la condición de filtrado que se especificó originalmente en la zona horaria local y después se convirtió a UTC mediante la cláusula AT TIME ZONE introducida en SQL Server 2016:The following code example shows how to apply filtering condition that is originally specified in the local time zone and then converted to UTC using AT TIME ZONE introduced in SQL Server 2016:

/*Add offset of the local time zone to current time*/  
DECLARE @asOf DATETIMEOFFSET = GETDATE() AT TIME ZONE 'Pacific Standard Time'  
/*Convert AS OF filter to UTC*/  
SET @asOf = DATEADD (MONTH, -9, @asOf) AT TIME ZONE 'UTC';  
  
SELECT   
    EmployeeID  
    , Name  
    , Position  
    , Department  
    , [Address]  
    , [AnnualSalary]  
    , ValidFrom AT TIME ZONE 'Pacific Standard Time' AS ValidFromPT   
    , ValidTo AT TIME ZONE 'Pacific Standard Time' AS ValidToPT  
FROM Employee   
    FOR SYSTEM_TIME AS OF @asOf where EmployeeId = 1000  
  

El uso de AT TIME ZONE es útil para todos los demás escenarios donde se usan tablas con versión del sistema.Using AT TIME ZONE is helpful in all other scenarios where system-versioned tables are used.

Sugerencia

Las condiciones de filtrado especificadas en cláusulas temporales con FOR SYSTEM_TIME son SARGABLE (es decir,Filtering conditions specified in temporal clauses with FOR SYSTEM_TIME are SARG-able (i.e SQL Server puede usar un índice agrupado subyacente para llevar a cabo una búsqueda en lugar de una operación de análisis.SQL Server can utilize underlying clustered index to perform a seek instead of a scan operation.
Si consulta directamente la tabla de historial, asegúrese de que la condición de filtrado también sea SARGABLE especificando los filtros en forma de <columna de período> {< | > | =, ...}If you query the history table directly, make sure that your filtering condition is also SARG-able by specifying filters in form of <period column> {< | > | =, ...} condición_fecha AT TIME ZONE "UTC".date_condition AT TIME ZONE 'UTC'.
Si aplica AT TIME ZONE a columnas de período, SQL Server realizará un examen de tabla o índice, lo cual puede resultar muy caro.If you apply AT TIME ZONE to period columns, SQL Server will perform a table/index scan, which can be very expensive. Evite este tipo de condición en las consultas:Avoid this type of condition in your queries:
<columna de periodo> AT TIME ZONE "<su zona horaria>" > {< | > | =, ...}<period column> AT TIME ZONE '<your time zone>' > {< | > | =, ...} condición_fecha.date_condition.

Vea también: Consulta de los datos de una tabla temporal con control de versiones del sistema.See also: Querying Data in a System-Versioned Temporal Table.

Análisis a un momento dado (viaje en el tiempo)Point in Time Analysis (Time Travel)

A diferencia de la auditoría de datos, donde el foco está normalmente en los cambios que se produjeron en registros individuales, en escenarios de viaje en el tiempo los usuarios desean ver cómo cambiaron los conjuntos de datos completos a lo largo del tiempo.Unlike data audit, where the focus is typically on changes that occurred to an individual records, in time travel scenarios users want to see how entire data sets changed over time. A veces, el viaje en el tiempo incluye varias tablas temporales relacionadas, cada una de ellas cambiando a un ritmo independiente, para las que desea analizar:Sometimes time travel includes several related temporal tables, each changing at independent pace, for which you want to analyze:

  • Tendencias de los indicadores importantes en los datos históricos y actualesTrends for the important indicators in the historical and current data

  • Instantánea exacta de todos los datos "a partir de" cualquier momento dado del pasado (ayer, hace un mes, etc.)Exact snapshot of the entire data "as of" any point in time in the past (yesterday, a month ago, etc.)

  • Diferencias entre dos momentos dados de interés (hace un mes frente a hace tres meses, por ejemplo)Differences in between two point in time of interest (a month ago vs. three months ago, for instance)

Hay muchos escenarios reales que requieren el análisis de viaje en el tiempo.There are many real-world scenarios which require time travel analysis. Para ilustrar este escenario de uso, echemos un vistazo a OLTP con historial generado automáticamente.To illustrate this usage scenario, let's look at OLTP with auto-generated history.

OLTP con historial de datos generado automáticamenteOLTP with Auto-Generated Data History

En sistemas de procesamiento de transacciones, no es inusual analizar cómo cambian las métricas importantes a lo largo del tiempo.In transaction processing systems, it is not unusual to analyze how important metrics change over time. Idealmente, analizar el historial no debería afectar al rendimiento de la aplicación OLTP donde el acceso al estado más reciente de los datos debe producirse con una latencia y bloqueo de datos mínimos.Ideally, analyzing history should not compromise performance of the OLTP application where access to the latest state of data must occur with minimal latency and data locking. Las tablas temporales con versión del sistema están diseñadas para permitir a los usuarios mantener de forma transparente el historial completo de cambios para su análisis posterior, independientemente de los datos actuales, con un impacto mínimo en la carga de trabajo OLTP principal.System-versioned temporal tables are designed to allow users to transparently keep the full history of changes for later analysis, separately from the current data, with the minimal impact on the main OLTP workload.
Para cargas de trabajo con mucho procesamiento de transacciones, se recomienda usar Tablas temporales con control de versiones del sistema con tablas con optimización para memoria, que permiten almacenar datos actuales en memoria y el historial completo de cambios en disco de una manera eficiente.For high transactional processing workloads, we recommend that you use System-Versioned Temporal Tables with Memory-Optimized Tables, which allow you to store current data in-memory and full history of changes on disk in a cost effective way.

Para la tabla de historial, se recomienda utilizar un índice de almacén de columnas agrupado por las razones siguientes:For the history table, we recommend that you use a clustered columnstore index for the following reasons:

  • El análisis de tendencias típico aprovecha el rendimiento de las consultas que proporciona un índice de almacén de columnas agrupado.Typical trend analysis benefits from query performance provided by a clustered columnstore index.

  • La tarea de vaciado de datos con tablas optimizadas para memoria funciona mejor con mucha carga de trabajo OLTP cuando la tabla de historial tiene un índice de almacén de columnas agrupado.The data flush task with memory-optimized tables performs best under heavy OLTP workload when the history table has a clustered columnstore index.

  • Un índice de almacén de columnas agrupado proporciona una compresión excelente, especialmente en escenarios donde no todas las columnas se cambian al mismo tiempo.A clustered columnstore index provides excellent compression, especially in scenarios where not all columns are changed at the same time.

El uso de tablas temporales con OLTP en memoria reduce la necesidad de mantener todo el conjunto de datos en memoria y permite distinguir fácilmente entre los datos activos e inactivos.Using temporal tables with in-memory OLTP reduces the need to keep the entire data set in-memory and enables you to easily distinguish between hot and cold data.
Ejemplos de escenarios reales que encajan bien en esta categoría son la administración de inventarios o la compraventa de divisas, entre otros.Examples of the real-world scenarios that fit well into this category are inventory management or currency trading, among others.

El diagrama siguiente muestra el modelo de datos simplificado utilizado para la administración de inventarios:The following diagram shows simplified data model used for inventory management:

UsoTemporalEnMemoriaTemporalUsageInMemory

En el ejemplo de código siguiente se crea ProductInventory como una tabla temporal con versión del sistema en memoria con un índice de almacén de columnas agrupado en la tabla de historial (que realmente reemplaza al índice de almacén de filas que se crea de forma predeterminada):The following code example creates ProductInventory as an in-memory system-versioned temporal table with a clustered columnstore index on the history table (which actually replaces the row-store index created by default):

Nota

Asegúrese de que la base de datos permite la creación de tablas optimizadas para memoria.Make sure that your database allows creation of memory-optimized tables. Vea Crear una tabla con optimización para memoria y un procedimiento almacenado compilado de forma nativa.See Creating a Memory-Optimized Table and a Natively Compiled Stored Procedure.

USE TemporalProductInventory  
GO  
  
BEGIN  
    --If table is system-versioned, SYSTEM_VERSIONING must be set to OFF first   
    IF ((SELECT temporal_type FROM SYS.TABLES WHERE object_id = OBJECT_ID('dbo.ProductInventory', 'U')) = 2)  
    BEGIN  
        ALTER TABLE [dbo].[ProductInventory] SET (SYSTEM_VERSIONING = OFF)  
    END  
    DROP TABLE IF EXISTS [dbo].[ProductInventory]  
       DROP TABLE IF EXISTS [dbo].[ProductInventoryHistory]  
END  
GO  
  
CREATE TABLE [dbo].[ProductInventory]  
(  
    ProductId int NOT NULL,  
    LocationID INT NOT NULL,  
    Quantity int NOT NULL CHECK (Quantity >=0),  
  
    SysStartTime datetime2(0) GENERATED ALWAYS AS ROW START  NOT NULL ,  
    SysEndTime datetime2(0) GENERATED ALWAYS AS ROW END  NOT NULL ,  
    PERIOD FOR SYSTEM_TIME(SysStartTime,SysEndTime),  
  
    --Primary key definition  
    CONSTRAINT PK_ProductInventory PRIMARY KEY NONCLUSTERED (ProductId, LocationId)  
)  
WITH  
(  
    MEMORY_OPTIMIZED=ON,      
    SYSTEM_VERSIONING = ON   
    (          
        HISTORY_TABLE = [dbo].[ProductInventoryHistory],          
        DATA_CONSISTENCY_CHECK = ON  
    )  
)  
  
CREATE CLUSTERED COLUMNSTORE INDEX IX_ProductInventoryHistory ON [ProductInventoryHistory]  
WITH (DROP_EXISTING = ON);  

Para el modelo anterior, este podría ser el aspecto del procedimiento para mantener el inventario:For the model above this is how the procedure for maintaining inventory could look like:

CREATE PROCEDURE [dbo].[spUpdateInventory]  
@productId int,  
@locationId int,  
@quantityIncrement int  
  
WITH NATIVE_COMPILATION, SCHEMABINDING  
AS  
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL=SNAPSHOT, LANGUAGE=N'English')  
    UPDATE dbo.ProductInventory  
        SET Quantity = Quantity + @quantityIncrement   
            WHERE ProductId = @productId AND LocationId = @locationId  
  
/*If zero rows were updated than this is insert of the new product for a given location*/  
    IF @@rowcount = 0  
        BEGIN  
            IF @quantityIncrement < 0  
                SET @quantityIncrement = 0  
            INSERT INTO [dbo].[ProductInventory]  
                (  
                    [ProductId]  
                    ,[LocationID]  
                    ,[Quantity]  
                )  
                VALUES  
                   (  
                        @productId  
                       ,@locationId  
                       ,@quantityIncrement  
        END  
END;  

El procedimiento almacenado spUpdateInventory inserta un nuevo producto en el inventario o actualiza la cantidad de productos para la ubicación específica.The spUpdateInventory stored procedure either inserts a new product in the inventory or updates the product quantity for the particular location. La lógica de negocios es muy sencilla y se centra en mantener la precisión del estado más reciente en todo momento incrementando o disminuyendo el campo Quantity a través de la actualización de la tabla, mientras que las tablas con versión del sistema agregan de forma transparente dimensión de historial a los datos, como se muestra en el diagrama siguiente.The business logic is very simple and focused on maintaining the latest state accurate all the time by incrementing / decrementing the Quantity field through table update, while system-versioned tables transparently add history dimension to the data, as depicted on the diagram below

UsoTemporalEnMemoria2bTemporalUsageInMemory2b

Ahora, la consulta del estado más reciente puede realizarse eficazmente desde el módulo compilado de forma nativa:Now, querying of the latest state can be performed efficiently from the natively compiled module:

CREATE PROCEDURE [dbo].[spQueryInventoryLatestState]  
WITH NATIVE_COMPILATION, SCHEMABINDING  
AS  
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL=SNAPSHOT, LANGUAGE=N'English')  
    SELECT ProductId, LocationID, Quantity, SysStartTime  
      FROM dbo.ProductInventory  
    ORDER BY ProductId, LocationId  
END;  
GO  
EXEC [dbo].[spQueryInventoryLatestState];  

El análisis de los datos a lo largo del tiempo pasa a ser una tarea muy sencilla con la cláusula FOR SYSTEM_TIME ALL, tal como se muestra en el ejemplo siguiente:Analyzing data changes over time becomes extremely easy with the FOR SYSTEM_TIME ALL clause, as shown in the following example:

DROP VIEW IF EXISTS vw_GetProductInventoryHistory;  
GO  
CREATE VIEW vw_GetProductInventoryHistory  
AS  
   SELECT ProductId, LocationId, Quantity, SysStartTime, SysEndTime   
   FROM [dbo].[ProductInventory]  
   FOR SYSTEM_TIME ALL;  
GO  
SELECT * FROM vw_GetProductInventoryHistory   
    WHERE ProductId = 2;  

El diagrama siguiente muestra el historial de datos de un producto que se puede representar fácilmente importando la vista anterior en Power Query, Power BI o una herramienta de inteligencia empresarial similar:The diagram below shows the data history for one product which can be easily rendered importing the view above in the Power Query, Power BI or similar business intelligence tool:

HistorialDeProductosEnElTiempoProductHistoryOverTime

Las tablas temporales se pueden utilizar en este escenario para realizar otros tipos de análisis de viaje en el tiempo, como reconstruir el estado de AS OF del inventario a cualquier momento dado del pasado o comparar instantáneas que pertenecen a diferentes momentos en el tiempo.Temporal tables can be used in this scenario to perform other types of time travel analysis, such as reconstructing the state of the inventory AS OF any point in time in the past or comparing snapshots that belong to different moments in time.

Para este escenario de uso, también puede extender las tablas Product y Location para convertirlas en tablas temporales, lo que permite un análisis posterior del historial de cambios de UnitPrice y NumberOfEmployee.For this usage scenario, you can also extend the Product and Location tables to become temporal tables, which enables later analysis of the history of changes of UnitPrice and NumberOfEmployee.

ALTER TABLE Product   
ADD   
    SysStartTime datetime2 (2) GENERATED ALWAYS AS ROW START HIDDEN    
        constraint DF_ValidFrom DEFAULT DATEADD(second, -1, SYSUTCDATETIME())  
    , SysEndTime datetime2 (2)  GENERATED ALWAYS AS ROW END HIDDEN     
        constraint DF_ValidTo DEFAULT '9999.12.31 23:59:59.99'  
    , PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);   
  
ALTER TABLE Product    
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProductHistory));  
  
ALTER TABLE [Location]  
ADD   
    SysStartTime datetime2 (2) GENERATED ALWAYS AS ROW START HIDDEN    
        constraint DFValidFrom DEFAULT DATEADD(second, -1, SYSUTCDATETIME())  
    , SysEndTime datetime2 (2)  GENERATED ALWAYS AS ROW END HIDDEN     
        constraint DFValidTo DEFAULT '9999.12.31 23:59:59.99'  
    , PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);  
  
ALTER TABLE [Location]    
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.LocationHistory));  

Puesto que el modelo de datos ahora implica varias tablas temporales, la práctica recomendada para el análisis AS OF es crear una vista que extraiga los datos necesarios de las tablas relacionadas y aplicar FOR SYSTEM_TIME AS OF a la vista, ya que esto simplificará considerablemente la reconstrucción del estado de todo el modelo de datos:Since the data model now involves multiple temporal tables, the best practice for AS OF analysis is to create a view that extracts necessary data from the related tables and apply FOR SYSTEM_TIME AS OF to the view as this will greatly simplify reconstructing the state of entire data model:

DROP VIEW IF EXISTS vw_ProductInventoryDetails;  
GO  
  
CREATE VIEW vw_ProductInventoryDetails  
AS  
    SELECT PrInv.ProductId ,PrInv.LocationId, P.ProductName, L.LocationName, PrInv.Quantity  
    , P.UnitPrice, L.NumberOfEmployees  
    , P.SysStartTime AS ProductStartTime, P.SysEndTime AS ProductEndTime  
    , L.SysStartTime AS LocationStartTime, L.SysEndTime AS LocationEndTime  
    , PrInv.SysStartTime AS InventoryStartTime, PrInv.SysEndTime AS InventoryEndTime  
FROM dbo.ProductInventory as PrInv  
JOIN dbo.Product AS P ON PrInv.ProductId = P.ProductID  
JOIN dbo.Location AS L ON PrInv.LocationId = L.LocationID;  
GO  
SELECT * FROM vw_ProductInventoryDetails  
    FOR SYSTEM_TIME AS OF '2015.01.01';   

En la siguiente imagen se muestra el plan de ejecución generado para la consulta SELECT.The following picture shows the execution plan generated for the SELECT query. Esto ilustra que el motor de SQL Server controla completamente toda la complejidad de trabajar con las relaciones temporales:This illustrates that all complexity of dealing with temporal relations is fully handled by the SQL Server engine:

PlanDeEjecuciónASOFASOFExecutionPlan

Utilice el código siguiente para comparar el estado del inventario de productos entre dos momentos dados (hace un día y hace un mes):Use the following code to compare state of product inventory between two points in time (a day ago and a month ago):

DECLARE @dayAgo datetime2 (0) = DATEADD (day, -1, SYSUTCDATETIME());  
DECLARE @monthAgo datetime2 (0) = DATEADD (month, -1, SYSUTCDATETIME());  
  
SELECT   
    inventoryDayAgo.ProductId  
    , inventoryDayAgo.ProductName  
    , inventoryDayAgo.LocationName  
    , inventoryDayAgo.Quantity AS QuantityDayAgo,inventoryMonthAgo.Quantity AS QuantityMonthAgo  
    , inventoryDayAgo.UnitPrice AS UnitPriceDayAgo, inventoryMonthAgo.UnitPrice AS UnitPriceMonthAgo  
FROM vw_ProductInventoryDetails  
FOR SYSTEM_TIME AS OF @dayAgo AS inventoryDayAgo  
JOIN vw_ProductInventoryDetails FOR SYSTEM_TIME AS OF @monthAgo AS inventoryMonthAgo  
    ON inventoryDayAgo.ProductId = inventoryMonthAgo.ProductId AND inventoryDayAgo.LocationId = inventoryMonthAgo.LocationID;  

Detección de anomalíasAnomaly Detection

La detección de anomalías (o detección de valores atípicos) es la identificación de los elementos que no cumplen un patrón esperado u otros elementos en un conjunto de datos.Anomaly detection (or outlier detection) is the identification of items which do not conform to an expected pattern or other items in a dataset.
Puede utilizar tablas temporales con versión del sistema para detectar anomalías que se producen periódica o irregularmente como puede usar consultas temporales para encontrar rápidamente patrones específicos.You can use system-versioned temporal tables to detect anomalies that occur periodically or irregularly as you can utilize temporal querying to quickly locate specific patterns.
De qué anomalías se trata depende del tipo de datos que se recopilan y de la lógica de negocios.What anomaly is depends on type of data you collect and your business logic.

En el ejemplo siguiente se muestra una lógica simplificada para detectar "picos" en las cifras de ventas.The following example shows simplified logic for detecting "spikes" in sales numbers. Supongamos que trabaja con una tabla temporal que recopila el historial de los productos comprados:Let's assume that you work with a temporal table that collects history of the products purchased:

CREATE TABLE [dbo].[Product]  
                (  
            [ProdID] [int] NOT NULL PRIMARY KEY CLUSTERED  
        , [ProductName] [varchar](100) NOT NULL  
        , [DailySales] INT NOT NULL  
        , [ValidFrom] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL  
        , [ValidTo] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL  
        , PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo])  
    )  
    WITH( SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[ProductHistory]   
        , DATA_CONSISTENCY_CHECK = ON ))  
  

El diagrama siguiente muestra las compras a lo largo del tiempo:The following diagram shows the purchases over time:

DetecciónDeAnomalíaTemporalTemporalAnomalyDetection

Suponiendo que durante los días normales el número de productos comprados tiene una pequeña variación, la siguiente consulta identifica los valores atípicos de singleton. Se trata de ejemplos cuya diferencia en comparación con sus vecinos inmediatos es considerable (el doble), mientras que las muestras adyacentes no difieren significativamente (menos del 20 %):Assuming that during the regular days number of purchased products has small variance, the following query identifies singleton outliers - samples which difference compared to their immediate neighbors is significant (2x), while surrounding samples do not differ significantly (less than 20%):

WITH CTE (ProdId, PrevValue, CurrentValue, NextValue, ValidFrom, ValidTo)  
AS  
    (  
        SELECT   
            ProdId, LAG (DailySales, 1, 1) over (partition by ProdId order by ValidFrom) as PrevValue  
            , DailySales, LEAD (DailySales, 1, 1) over (partition by ProdId order by ValidFrom) as NextValue   
             , ValidFrom, ValidTo from Product  
        FOR SYSTEM_TIME ALL  
)  
  
SELECT   
    ProdId  
    , PrevValue  
    , CurrentValue  
    , NextValue  
    , ValidFrom  
    , ValidTo  
    , ABS (PrevValue - NextValue) / convert (float, (CASE WHEN NextValue > PrevValue THEN PrevValue ELSE NextValue END)) as PrevToNextDiff  
    , ABS (CurrentValue - PrevValue) / convert (float, (CASE WHEN CurrentValue > PrevValue THEN PrevValue ELSE CurrentValue END)) as CurrentToPrevDiff  
    , ABS (CurrentValue - NextValue) / convert (float, (CASE WHEN CurrentValue > NextValue THEN NextValue ELSE CurrentValue END)) as CurrentToNextDiff  
FROM CTE   
    WHERE   
        ABS (PrevValue - NextValue) / (CASE WHEN NextValue > PrevValue THEN PrevValue ELSE NextValue END) < 0.2  
            AND ABS (CurrentValue - PrevValue) / (CASE WHEN CurrentValue > PrevValue THEN PrevValue ELSE CurrentValue END) > 2  
            AND ABS (CurrentValue - NextValue) / (CASE WHEN CurrentValue > NextValue THEN NextValue ELSE CurrentValue END) > 2;  

Nota

Este ejemplo está simplificado deliberadamente.This example is intentionally simplified. En los escenarios de producción, probablemente utilizaría avanzados métodos estadísticos para identificar las muestras que no siguen el patrón común.In the production scenarios, you would likely use advanced statistical methods to identify samples which do not follow the common pattern.

Dimensiones de variación lentaSlowly-Changing Dimensions

Las dimensiones de almacenamiento de datos normalmente contienen datos relativamente estáticos sobre entidades como ubicaciones geográficas, clientes o productos.Dimensions in data warehousing typically contain relatively static data about entities such as geographical locations, customers, or products. Sin embargo, algunos escenarios también requieren realizar el seguimiento de cambios de datos en tablas de dimensiones.However, some scenarios require you to track data changes in dimension tables as well. Dado que la modificación en las dimensiones ocurre con mucha menos frecuencia, de una forma impredecible y fuera de la programación de actualizaciones normal que se aplica a tablas de hechos, estos tipos de tablas de dimensiones se denominan dimensiones de variación lenta (DVL).Given that modification in dimensions happen much less frequently, in unpredictable manner and outside of the regular update schedule that applies to fact tables, these types of dimension tables are called slowly changing dimensions (SCD).

Existen varias categorías de dimensiones de variación lenta basadas en la forma de conservar el historial de cambios:There are several categories of slowly changing dimensions based on how history of changes is preserved:

  • Tipo 0: No se conserva el historial.Type 0: History is not preserved. Los atributos de dimensión reflejan los valores originales.Dimension attributes reflect original values.

  • Tipo 1: Los atributos de dimensión reflejan los valores más recientes (los valores anteriores se sobrescriben).Type 1: Dimension attributes reflect latest values (previous values are overwritten)

  • Tipo 2: Cada versión de miembro de dimensión se representa con una fila independiente en la tabla normalmente con columnas que representan el período de validez.Type 2: Every version of dimension member represented with separate row in the table usually with columns that represent period of validity

  • Tipo 3: Mantener el historial limitado para los atributos seleccionados usando columnas adicionales en la misma fila.Type 3: Keeping limited history for selected attribute(s) using additional columns in the same row

  • Tipo 4: Mantener el historial en la tabla independiente mientras la tabla de dimensiones original mantiene las versiones de miembro de dimensión más recientes (actual).Type 4: Keeping history in the separate table while original dimension table keeps latest (current) dimension member versions

Cuando se elige la estrategia DVL, es responsabilidad de la capa Extraer-Transformar-Cargar (ETL, Extraer-Transformar-Cargar) mantener la precisión de las tablas de dimensiones, tarea que normalmente requiere una gran cantidad de código y un mantenimiento complejo.When you choose SCD strategy, it is responsibility of the ETL layer (Extract-Transform-Load) to keep dimension table(s) accurate and that usually requires a lot of code and complex maintenance.

Las tablas temporales con versión del sistema de SQL Server 2016 pueden utilizarse para reducir considerablemente la complejidad del código ya que el historial de los datos cuando se conserva automáticamente.System-versioned temporal tables in SQL Server 2016 can be used to dramatically lower the complexity of your code as history of data is automatically preserved. Dada su implementación con dos tablas, las tablas temporales de SQL Server 2016 es los más próximo a DVL de tipo 4.Given its implementation using two tables, temporal tables in SQL Server 2016 is closest to Type 4 SCD. Sin embargo, puesto que las consultas temporales solo permiten hacer referencia a la tabla actual, también puede plantearse el uso de tablas temporales en entornos donde piensa usar DVL de tipo 2.However, since temporal queries allows you to reference current table only, you can also consider temporal tables in environments where you plan to use Type 2 SCD.

Para convertir la dimensión normal en DVL, basta con crear una nueva o modificar una existente para convertirse en una tabla temporal con versión del sistema.In order to convert your regular dimension to SCD, just create a new one or alter an existing one to become a system-versioned temporal table. Si la tabla de dimensiones existente contiene datos históricos, cree una tabla independiente y mueva los datos históricos allí, y mantenga las versiones de dimensión actuales (reales) en la tabla de dimensiones original.If your existing dimension table contains historical data, create separate table and move historical data there and keep current (actual) dimension versions in your original dimension table. Después, utilice la sintaxis ALTER TABLE para convertir la tabla de dimensiones a una tabla temporal con versión del sistema con una tabla de historial predefinida.Then use ALTER TABLE syntax to convert your dimension table to a system-versioned temporal table with a predefined history table.

En el ejemplo siguiente se ilustra el proceso y se supone que la tabla de dimensiones DimLocation ya tiene ValidFrom y ValidTo como columnas que no admiten valores NULL datetime2 y que el proceso ETL rellena:The following example illustrates the process and assumes that the DimLocation dimension table already has ValidFrom and ValidTo as datetime2 non-nullable columns which are populated by the ETL process:

/*Move "closed" row versions into newly created history table*/  
SELECT * INTO  DimLocationHistory  
    FROM DimLocation  
        WHERE ValidTo < '9999-12-31 23:59:59.99';  
GO  
/*Create clustered columnstore index which is a very good choice in DW scenarios*/  
CREATE CLUSTERED COLUMNSTORE INDEX IX_DimLocationHistory ON DimLocationHistory  
/*Delete previous versions from DimLocation which will become current table in temporal-system-versioning configuration*/  
DELETE FROM DimLocation  
    WHERE ValidTo < '9999-12-31 23:59:59.99';  
/*Add period definition*/  
ALTER TABLE DimLocation ADD PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);  
/*Enable system-versioning and bind histiory table to the DimLocation*/  
ALTER TABLE DimLocation SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DimLocationHistory));  

Tenga en cuenta que no se necesita código adicional para mantener DVL durante el proceso de carga de almacenamiento de datos una vez que se ha creado.Note that no additional code is required to maintain SCD during the data warehouse loading process once you created it.

La siguiente ilustración muestra cómo puede utilizar las tablas temporales en un escenario sencillo que implica 2 DVL (DimLocation y DimProduct) y una tabla de hechos.The following illustration shows how you can use Temporal Tables in a simple scenario involving 2 SCDs (DimLocation and DimProduct) and one fact table.

SCDTemporalTemporalSCD

Para poder utilizar las DVL en los informes, debe ajustar eficazmente las consultas.In order to use above SCDs in reports, you need to effectively adjust querying. Por ejemplo, puede que le interese calcular la cantidad total de ventas y el promedio de productos vendidos per cápita durante los últimos seis meses.For example, you might want to calculate the total sales amount and the average number of sold products per capita for the last six months. Observe que ambas métricas requieren la correlación de datos de la tabla de hechos y las dimensiones que podrían haber cambiado sus atributos importantes para el análisis (DimLocation.NumOfCustomers, DimProduct.UnitPrice).Note that both metrics requires the correlation of data from the fact table and dimensions that might have changed their attributes important for the analysis (DimLocation.NumOfCustomers, DimProduct.UnitPrice). La siguiente consulta calcula correctamente las métricas requeridas:The followinq query properly calculates the required metrics:

DECLARE @now datetime2 = SYSUTCDATETIME()  
DECLARE @sixMonthsAgo datetime2 SET   
    @sixMonthsAgo = DATEADD (month, -12, SYSUTCDATETIME())   
  
SELECT DimProduct_History.ProductId  
   , DimLocation_History.LocationId  
    , SUM(F.Quantity * DimProduct_History.UnitPrice) AS TotalAmount  
    , AVG (F.Quantity/DimLocation_History.NumOfCustomers) AS AverageProductsPerCapita   
FROM FactProductSales F   
/* find corresponding record in SCD history in last 6 months, based on matching fact */  
JOIN DimLocation FOR SYSTEM_TIME BETWEEN @sixMonthsAgo AND @now AS DimLocation_History   
    ON DimLocation_History.LocationId = F.LocationId   
        AND F.FactDate BETWEEN DimLocation_History.ValidFrom AND DimLocation_History.ValidTo   
/* find corresponding record in SCD history in last 6 months, based on matching fact */  
JOIN DimProduct FOR SYSTEM_TIME BETWEEN @sixMonthsAgo AND @now AS DimProduct_History   
    ON DimProduct_History.ProductId = F.ProductId  
        AND F.FactDate BETWEEN DimProduct_History.ValidFrom AND DimProduct_History.ValidTo   
    WHERE F.FactDate BETWEEN @sixMonthsAgo AND @now   
GROUP BY DimProduct_History.ProductId, DimLocation_History.LocationId ;  

Consideraciones:Considerations:

  • El uso de tablas temporales con versión del sistema para DVL es aceptable si el período de validez calculado en función del tiempo de transacción de base de datos está bien con la lógica de negocios.Using system-versioned temporal tables for SCD is acceptable if period of validity calculated based on database transaction time is fine with your business logic. Si carga los datos con un retraso importante, el tiempo de la transacción puede no ser aceptable.If you load data with significant delay, transaction time might not be acceptable.

  • De forma predeterminada, las tablas temporales con versión del sistema no permiten cambiar los datos históricos después de la carga (puede modificar el historial después de establecer SYSTEM_VERSIONING en OFF).By default, system-versioned temporal tables do not allow changing historical data after loading (you can modify history after you set SYSTEM_VERSIONING to OFF). Esto podría ser la limitación en casos donde el cambio de datos históricos ocurre con regularidad.This might be limitation in cases where changing historical data happens regularly.

  • Las tablas temporales con versión del sistema generan versión de fila en cualquier cambio de columna.Temporal system-versioned tables generate row version on any column change. Si desea suprimir nuevas versiones en ciertos cambios de columna debe incorporar esa limitación en la lógica ETL.If you want to suppress new versions on certain column change you need to incorporate that limitation in the ETL logic.

  • Si espera un número significativo de filas históricas en tablas de DVL, considere el uso de un índice de almacén de columnas agrupado como la opción de almacenamiento principal para la tabla de historial.If you expect a significant number of historical rows in SCD tables, consider using a clustered columnstore index as the main storage option for history table. Eso reducirá la superficie de la tabla de historial y acelerará las consultas analíticas.That will reduce history table footprint and speed up your analytical queries.

Reparación de daños en los datos de filaRepairing Row-Level Data Corruption

Puede basarse en los datos históricos de las tablas temporales con versión del sistema para reparar rápidamente filas individuales a cualquiera de los estados capturados anteriormente.You can rely on historical data in system-versioned temporal tables to quickly repair individual rows to any of the previously captured states. Esta propiedad de tablas temporales es muy útil cuando es posible localizar filas afectadas y/o cuando conoce la hora del cambio de datos no deseado de forma que puede realizar la reparación de manera muy eficaz sin ocuparse de las copias de seguridad.This property of temporal tables is very useful when you are able to locate affected rows and/or when you know time of undesired data change so you can perform repair very efficiently without dealing with backups.

Este enfoque presenta una serie de ventajas:This approach has several advantages:

  • Es posible controlar el ámbito de la reparación de manera muy precisa.You are able to control the scope of the repair very precisely. Los registros que no se ven afectados deben permanecer en el estado más reciente, que suele ser un requisito crítico.Records that are not affected need to stay at the latest state, which is often a critical requirement.

  • La operación es muy eficaz y la base de datos permanece en línea para todas las cargas de trabajo usando los datos.Operation is very efficient and the database stays online for all workloads using the data.

  • La propia operación de reparación es con versiones.The repair operation itself is versioned. Tendrá la pista de auditoría de la propia operación de reparación, por lo que puede analizar qué ocurrió posteriormente si es necesario.You have audit trail for repair operation itself, so you can analyze what happened later if necessary.

La acción de reparación puede automatizarse con relativa facilidad.Repair action can be automated relatively easily. Este es el ejemplo de código del procedimiento almacenado que realiza la reparación de datos para la tabla Employee usada en el escenario de auditoría de datos.Here is code example of the stored procedure that performs data repair for the table Employee used in the data audit scenario.

DROP PROCEDURE IF EXISTS sp_RepairEmployeeRecord;  
GO  
  
CREATE PROCEDURE sp_RepairEmployeeRecord   
    @EmployeeID INT,  
    @versionNumber INT = 1  
AS  
  
;WITH History  
AS  
(  
        /* Order historical rows by tehir age in DESC order*/  
        SELECT ROW_NUMBER () OVER (PARTITION BY EmployeeID ORDER BY [ValidTo] DESC) AS RN, *  
        FROM Employee FOR SYSTEM_TIME ALL WHERE YEAR (ValidTo) < 9999 AND Employee.EmployeeID = @EmployeeID  
)  
  
/*Update current row by using N-th row version from history (default is 1 - i.e. last version)*/  
UPDATE Employee   
    SET [Position] = H.[Position], [Department] = H.Department, [Address] = H.[Address], AnnualSalary = H.AnnualSalary  
    FROM Employee E JOIN History H ON E.EmployeeID = H.EmployeeID AND RN = @versionNumber  
    WHERE E.EmployeeID = @EmployeeID  
  

Este procedimiento almacenado toma @EmployeeID y @versionNumber como parámetros de entrada.This stored procedure takes @EmployeeID and @versionNumber as input parameters. Este procedimiento restaura de forma predeterminada el estado de fila a la versión más reciente a partir del historial (@versionNumber = 1).This procedure by default restores row state to the last version from the history (@versionNumber = 1).

La siguiente imagen muestra el estado de la fila antes y después de la invocación del procedimiento.The following picture shows state of the row before and after the procedure invocation. El rectángulo rojo marca la versión de fila actual que no es correcta, mientras que el rectángulo verde marca la versión correcta del historial.Red rectangle marks current row version that is incorrect, while green rectangle marks correct version from the history.

ReparaciónDeUsoTemporal1TemporalUsageRepair1

EXEC sp_RepairEmployeeRecord @EmployeeID = 1, @versionNumber = 1  

ReparaciónDeUsoTemporal2TemporalUsageRepair2

Este procedimiento almacenado de reparación puede definirse para aceptar una marca de tiempo exacta en lugar de la versión de fila.This repair stored procedure can be defined to accept an exact timestamp instead of row version. Restaurará la fila a cualquier versión que estuviera activa para el momento dado proporcionado (es decir, el momento dado AS OF).It will restore row to any version that was active for the point in time provided (i.e. AS OF point in time).

DROP PROCEDURE IF EXISTS sp_RepairEmployeeRecordAsOf;  
GO  
  
CREATE PROCEDURE sp_RepairEmployeeRecordAsOf   
    @EmployeeID INT,  
    @asOf datetime2  
AS  
  
/*Update current row to the state that was actual AS OF provided date*/  
UPDATE Employee   
    SET [Position] = History.[Position], [Department] = History.Department, [Address] = History.[Address], AnnualSalary = History.AnnualSalary  
    FROM Employee AS E JOIN Employee FOR SYSTEM_TIME AS OF @asOf AS History  ON E.EmployeeID = History.EmployeeID  
    WHERE E.EmployeeID = @EmployeeID  
  

Para el mismo ejemplo de datos la siguiente imagen ilustra el escenario de reparación con la condición de tiempo.For the same data sample the following picture illustrates repair scenario with time condition. Se resaltan el parámetro @asOf, la fila seleccionada en el historial que era real en el momento dado proporcionado y la nueva versión de fila en la tabla actual después de la operación de reparación:Highlighted are @asOf parameter, selected row in the history that was actual at the provided point in time and new row version in the current table after repair operation:

ReparaciónDeUsoTemporal3TemporalUsageRepair3

La corrección de datos puede convertirse en parte de la carga de datos automatizada en el almacenamiento de datos y los sistemas de informes.Data correction can become part of automated data loading in data warehousing and reporting systems.
Si un valor recién actualizado no es correcto entonces, en muchos escenarios, la restauración de la versión anterior del historial es una mitigación suficientemente buena.If a newly updated value is not correct then, in many scenarios, restoring the previous version from history is good enough mitigation. En el siguiente diagrama se muestra cómo se puede automatizar este proceso:The following diagram shows how this process can be automated:

ReparaciónDeUsoTemporal4TemporalUsageRepair4

Consulte tambiénSee Also

Tablas temporales Temporal Tables
Introducción a las tablas temporales con versión del sistema Getting Started with System-Versioned Temporal Tables
Comprobaciones de coherencia del sistema de la tabla temporal Temporal Table System Consistency Checks
Creación de particiones con tablas temporales Partitioning with Temporal Tables
Limitaciones y consideraciones de las tablas temporales Temporal Table Considerations and Limitations
Seguridad de la tabla temporal Temporal Table Security
Tablas temporales con control de versiones del sistema con tablas con optimización para memoria System-Versioned Temporal Tables with Memory-Optimized Tables
Funciones y vistas de metadatos de la tabla temporalTemporal Table Metadata Views and Functions