Сценарии использования темпоральных таблицTemporal table usage scenarios

Применимо к:Applies to: даSQL ServerSQL Server (все поддерживаемые версии) yesSQL ServerSQL Server (all supported versions) Применимо к:Applies to: даSQL ServerSQL Server (все поддерживаемые версии) yesSQL ServerSQL Server (all supported versions)

Темпоральные таблицы обычно используются в сценариях, в которых требуется отслеживание журнала изменений данных.Temporal Tables are generally useful in scenarios that require tracking history of data changes. Мы рекомендуем использовать темпоральные таблицы в следующих случаях, поскольку они дают существенный выигрыш в производительности:We recommend you to consider Temporal Tables in the following use cases for major productivity benefits.

Аудит данныхData audit

Используйте темпоральное системное управление версиями в таблицах, хранящих важные сведения, для которых необходимо отслеживать, что и когда было изменено, а также выполнять экспертизу данных на какой-либо момент времени.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.

Темпоральные таблицы с системным управлением версиями позволяют планировать сценарии аудита данных на ранних стадиях цикла разработки или добавлять аудит данных в существующие приложения или решения, когда он необходим.Temporal system-versioned tables allow 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.

На следующей схеме показан сценарий таблицы Employee с примером данных, включая текущие (помеченные синим цветом) и предыдущие версии строк (помеченные серым цветом).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). В правой части схемы показаны версии строк по оси времени, а также какие строки выбираются в разных типах запросов к темпоральной таблице с предложением 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.

TemporalUsageScenario1TemporalUsageScenario1

Включение системного управления версиями в новой таблице для аудита данныхEnabling system-versioning on a new table for data audit

Если вы определили информацию, для которой необходим аудит данных, создайте таблицы базы данных как темпоральные с системным управлением версиями.If you have identified information that needs data audit, create database tables as temporal system-versioned. В следующем простом примере показан сценарий с информацией в таблице Employee в гипотетической базе данных HR.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));

Разные варианты создания темпоральной таблицы с системным управлением версиями описываются в разделе Создание темпоральной таблицы с системным управлением версиями.Various options to create temporal system-versioned table are described in Creating a System-Versioned Temporal Table.

Включение системного управления версиями в существующей таблице для аудита данныхEnabling system-versioning on an existing table for data audit

Если требуется выполнить аудит данных в существующих базах данных, используйте инструкцию ALTER TABLE, чтобы добавить системное управление версиями в таблицы, не являющиеся темпоральными.If you need to perform data audit in existing databases, use ALTER TABLE to extend non-temporal tables to become system-versioned. Во избежание критических изменений в приложении добавляйте столбцы периода как скрытые (HIDDEN), как объясняется в разделе Замена не являющихся темпоральными таблиц темпоральными таблицами с системным управлением версиями.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. В следующем примере показано включение системного управления версиями в существующей таблице Employee в гипотетической базе данных HR.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));

Важно!

Точность типа данных datetime2 одинакова в исходной таблице и в таблице журнала с системным управлением версиями.The precision of the datetime2 datatype is the same in the source table and in the system-versioned history table.

После выполнения приведенного выше скрипта все изменения данных будут прозрачно собираться в таблице журнала.After executing the above script, all data changes will be collected transparently in the history table. В типичном сценарии аудита данных запрашиваются все изменения данных, примененные к отдельной строке в течение интересующего периода времени.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. Таблица журнала по умолчанию создается со сбалансированным деревом кластеризованного хранилища строк для эффективного решения этого варианта использования.The default history table is created with clustered row-store B-Tree to efficiently address this use case.

Выполнение анализа данныхPerforming data analysis

После включения системного управления версиями с помощью любого из перечисленных выше методов достаточно одного запроса, чтобы выполнить аудит данных.After enabling system-versioning using either of the above approaches, data audit is just one query away from you. Следующий запрос ищет версии строк для записи о сотруднике с EmployeeID = 1000, которые были активны по крайней мере часть времени между 1 января 2014 г. и 1 января 2015 г. (включая верхнюю границу периода).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;

Чтобы проанализировать весь журнал изменения данных для этого конкретного сотрудника, замените FOR SYSTEM_TIME BETWEEN...AND на SYSTEM_TIME ALL: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;

Чтобы найти версии строк, которые были активны только в течение некоторого периода (но не вне его), используйте предложение CONTAINED IN.To search for row versions that were active only within a period (and not outside of it), use CONTAINED IN. Этот запрос очень эффективен, поскольку запрашивает только таблицу журнала: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;

Наконец, в некоторых сценариях аудита может потребоваться узнать, как выглядела вся таблица в какой-либо момент времени в прошлом: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' ;

Темпоральные таблицы с системным управлением версиями хранят значения для столбцов периода в часовом поясе UTC, хотя всегда удобнее работать с местным часовым поясом и для фильтрации данных, и для отображения результатов.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. В следующих примерах кода показано, как применять условие фильтрации, которое изначально указывается в местном часовом поясе, а затем преобразуется в UTC с помощью предложения AT TIME ZONE, появившегося в 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

Предложение AT TIME ZONE удобно использовать во всех сценариях, где применяются таблицы с системным управлением версиями.Using AT TIME ZONE is helpful in all other scenarios where system-versioned tables are used.

Совет

Условия фильтрации, указанные в темпоральных предложениях с FOR SYSTEM_TIME, поддерживают SARG (т. е.Filtering conditions specified in temporal clauses with FOR SYSTEM_TIME are SARG-able (i.e SQL Server может использовать соответствующий базовый кластеризованный индекс для выполнения поиска вместо операции сканирования.SQL Server can utilize underlying clustered index to perform a seek instead of a scan operation. При выполнении прямого запроса к таблице журнала важно, чтобы используемое условие фильтра поддерживало SARG. Для этого указывайте фильтры в формате <period column> {< | > | =, ...}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> {< | > | =, ...} date_condition AT TIME ZONE 'UTC'.date_condition AT TIME ZONE 'UTC'. Если применить AT TIME ZONE к столбцам периода, то SQL Server будет выполнять сканирование таблицы или индекса, что может обходиться очень дорого.If you apply AT TIME ZONE to period columns, SQL Server will perform a table/index scan, which can be very expensive. Избегайте подобных условий в запросах: <period column> AT TIME ZONE '<your time zone>' > {< | > | =, ...}Avoid this type of condition in your queries: <period column> AT TIME ZONE '<your time zone>' > {< | > | =, ...} date_condition.date_condition.

См. также Запрос данных в темпоральной таблице с системным управлением версиями.See also: Querying Data in a System-Versioned Temporal Table.

Анализ на определенный момент времени (переход во времени)Point-in-time analysis (time travel)

В отличие от аудита данных, где обычно внимание сосредоточено на изменениях, произошедших с отдельными записями, в сценариях перехода во времени пользователи хотят видеть, как меняется со временем весь набор данных.Unlike data audit, where the focus is typically on changes that occurred to individual records, in time travel scenarios users want to see how entire data sets changed over time. Иногда переход во времени включает несколько связанных темпоральных таблиц, изменяющихся в независимом темпе, для которых требуется анализировать следующее:Sometimes time travel includes several related temporal tables, each changing at independent pace, for which you want to analyze:

  • тренды для важных индикаторов в исторических и текущих данных;Trends for the important indicators in the historical and current data
  • точный моментальный снимок всех данных на какой-либо момент времени в прошлом (вчера, месяц назад и т. д.);Exact snapshot of the entire data "as of" any point in time in the past (yesterday, a month ago, etc.)
  • различия между двумя интересующими моментами времени (например, между данными месяц назад и данными три месяца назад).Differences in between two points in time of interest (a month ago vs. three months ago, for instance)

Существует много реальных сценариев, в которых требуется анализ с переходом во времени.There are many real-world scenarios which require time travel analysis. Чтобы проиллюстрировать этот сценарий использования, давайте взглянем на OLTP с автоматически создаваемым журналом.To illustrate this usage scenario, let's look at OLTP with auto-generated history.

OLTP с автоматически создаваемым журналом данныхOLTP with auto-generated data history

В системах транзакционной обработки анализ того, как важные метрики изменяются с течением времени, не является чем-то необычным.In transaction processing systems, it is not unusual to analyze how important metrics change over time. В идеальном случае анализ журнала не должен ухудшать производительность приложения OLTP, где доступ к актуальному состоянию данных должен осуществляться с минимальной задержкой и блокировкой данных.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. Чтобы пользователи могли прозрачно хранить полный журнал изменений для дальнейшего анализа отдельно от текущих данных, с минимальным влиянием на основную рабочую нагрузку OLTP, были разработаны темпоральные таблицы с системным управлением версиями.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.

При высоких рабочих нагрузках транзакционной обработки рекомендуется использовать темпоральные таблицы с системным управлением версиями и таблицы, оптимизированные для операций в памяти, которые позволяют хранить текущие данные в памяти, а полный журнал изменений — на диске наиболее экономичным способом.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.

Для таблицы журнала рекомендуется использовать кластеризованный индекс columnstore по следующим причинам.For the history table, we recommend that you use a clustered columnstore index for the following reasons:

  • Типичный анализ трендов использует преимущества, предоставляемые кластеризованным индексом columnstore.Typical trend analysis benefits from query performance provided by a clustered columnstore index.
  • Задача очистки данных с оптимизированными для памяти таблицами при большой рабочей нагрузке OLTP выполняется лучше, когда таблица журнала имеет кластеризованный индекс columnstore.The data flush task with memory-optimized tables performs best under heavy OLTP workload when the history table has a clustered columnstore index.
  • Кластеризованный индекс columnstore обеспечивает отличное сжатие, особенно в сценариях, где не все столбцы изменяются в одно и то же время.A clustered columnstore index provides excellent compression, especially in scenarios where not all columns are changed at the same time.

Использование темпоральных таблиц с OLTP в памяти сокращает необходимость сохранять весь набор данных в памяти и позволяет легко различать "горячие" и "холодные" данные.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.

В качестве примеров реальных ситуаций, попадающих в эту категорию, можно среди прочего указать управление запасами и валютные операции.Examples of the real-world scenarios that fit well into this category are inventory management or currency trading, among others.

На следующей схеме показана упрощенная модель данных, используемая для управления запасами.The following diagram shows simplified data model used for inventory management:

TemporalUsageInMemoryTemporalUsageInMemory

В следующем примере кода создается таблица ProductInventory как темпоральная таблица с системным управлением версиями в памяти с кластеризованным индексом columnstore в таблице журнала (который фактически заменяет индекс хранилища строк, создаваемый по умолчанию):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):

Примечание

Убедитесь, что база данных позволяет создавать таблицы, оптимизированные для памяти.Make sure that your database allows creation of memory-optimized tables. См. раздел Создание таблицы с оптимизацией памяти и хранимой процедуры, скомпилированной в собственном коде.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 GENERATED ALWAYS AS ROW START NOT NULL ,
    SysEndTime datetime2 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);

Для модели выше процедура для обслуживания запасов может выглядеть так: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;

Хранимая процедура SpUpdateInventory либо вставляет новый продукт на склад, либо обновляет количество продуктов для определенного расположения.The spUpdateInventory stored procedure either inserts a new product in the inventory or updates the product quantity for the particular location. Бизнес-логика очень проста и заключается в поддержании постоянной точности актуального состояния путем увеличения или уменьшения значения поля Quantity через обновление таблицы, при этом таблицы с системным управлением версиями прозрачно добавляют измерение журнала к данным, как показано на следующей схеме.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.

TemporalUsageInMemory2bTemporalUsageInMemory2b

Теперь запрос актуального состояния может выполняться эффективно из модуля, скомпилированного в собственном коде: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];

Анализ изменений данных с течением времени становится очень простым с использованием предложения FOR SYSTEM_TIME ALL, как показано в следующем примере.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;

На схеме ниже показан журнал данных для одного продукта, который можно легко отобразить, импортировав представление выше в Power Query, Power BI или аналогичное средство бизнес-аналитики: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:

ProductHistoryOverTimeProductHistoryOverTime

Темпоральные таблицы могут использоваться в этом сценарии для выполнения других типов анализа с переходом во времени, например для восстановления состояния запасов в любой момент времени в прошлом или для сравнения моментальных снимков, относящихся к разным моментам времени.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.

Кроме того, в этом сценарии использования можно расширить таблицы Product и Location до темпоральных таблиц, что даст возможность последующего анализа журнала изменений UnitPrice и NumberOfEmployee.For this usage scenario, you can also extend the Product and Location tables to become temporal tables to enable later analysis of the history of changes of UnitPrice and NumberOfEmployee.

ALTER TABLE Product
ADD
    SysStartTime datetime2 GENERATED ALWAYS AS ROW START HIDDEN
        constraint DF_ValidFrom DEFAULT DATEADD(second, -1, SYSUTCDATETIME())
    , SysEndTime datetime2 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 GENERATED ALWAYS AS ROW START HIDDEN
        constraint DFValidFrom DEFAULT DATEADD(second, -1, SYSUTCDATETIME())
    , SysEndTime datetime2 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));

Поскольку теперь модель данных включает несколько темпоральных таблиц, для анализа AS OF (на момент времени) рекомендуется создать представление, которое извлекает необходимые данные из связанных таблиц, и применить к нему предложение SYSTEM_TIME AS OF, так как это сильно упростит восстановление состояния всей модели данных: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';

На следующем рисунке показан план выполнения, созданный для запроса SELECT.The following picture shows the execution plan generated for the SELECT query. Это показывает, что вся сложность работы с темпоральными отношениями полностью обрабатывается ядром SQL Server:This illustrates that all complexity of dealing with temporal relations is fully handled by the SQL Server engine:

ASOFExecutionPlanASOFExecutionPlan

Для сравнения состояния складских запасов в два момента времени (день назад и месяц назад) используйте следующий код: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 = DATEADD (day, -1, SYSUTCDATETIME());
DECLARE @monthAgo datetime2 = 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;

Обнаружение аномалийAnomaly detection

Обнаружение аномалий (или обнаружение выбросов) представляет собой выявление элементов, которые не соответствуют ожидаемому шаблону или другим элементам в наборе данных.Anomaly detection (or outlier detection) is the identification of items which do not conform to an expected pattern or other items in a dataset. Для обнаружения аномалий, возникающих периодически или нерегулярно, можно использовать темпоральные таблицы с системным управлением версиями, поскольку вы можете быстро находить определенные шаблоны с помощью темпоральных запросов.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. Вид аномалии зависит от собираемого типа данных и бизнес-логики.What anomaly is depends on type of data you collect and your business logic.

В следующем примере показана упрощенная логика для обнаружения "всплесков" в цифрах продаж.The following example shows simplified logic for detecting "spikes" in sales numbers. Предположим, что вы работаете с темпоральной таблицей, в которой собирается журнал приобретенных продуктов.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] GENERATED ALWAYS AS ROW START NOT NULL
        , [ValidTo] [datetime2] 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 ))

На следующей схеме показаны покупки с течением времени.The following diagram shows the purchases over time:

TemporalAnomalyDetectionTemporalAnomalyDetection

При условии что в обычные дни количество приобретенных продуктов имеет небольшой разброс, следующий запрос определяет единичные всплески — образцы, которые значительно отличаются от своих ближайших соседей (вдвое), тогда как окружающие образцы отличаются не сильно (менее чем на 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;

Примечание

Этот пример преднамеренно упрощен.This example is intentionally simplified. В рабочих сценариях для определения образцов, которые не соответствуют общему шаблону, вы, скорее всего, будете использовать расширенные статистические методы.In the production scenarios, you would likely use advanced statistical methods to identify samples which do not follow the common pattern.

Медленно изменяющиеся измеренияSlowly-changing dimensions

Измерения в хранилищах данных обычно содержат относительно статические данные о сущностях, таких как географические расположения, клиенты и продукты.Dimensions in data warehousing typically contain relatively static data about entities such as geographical locations, customers, or products. Однако в некоторых сценариях требуется отслеживание изменений данных и в таблицах измерений.However, some scenarios require you to track data changes in dimension tables as well. Учитывая, что изменения в измерениях происходят гораздо реже, непредсказуемо и вне рамок расписания регулярного обновления, которое применяется к таблицам фактов, такие типы таблиц измерений называются медленно изменяющимися измерениями (SCD).Given that modifications 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).

Существует несколько категорий медленно изменяющихся измерений, выделяемых на основе того, как сохраняется журнал изменений.There are several categories of slowly changing dimensions based on how history of changes is preserved:

  • Тип 0. Журнал не сохраняется.Type 0: History is not preserved. Атрибуты измерений отражают исходные значения.Dimension attributes reflect original values.
  • Тип 1. Атрибуты измерений отражают последние значения (предыдущие значения перезаписываются).Type 1: Dimension attributes reflect latest values (previous values are overwritten)
  • Тип 2. Каждая версия элемента измерения представлена в виде отдельной строки таблицы, обычно со столбцами, представляющими период действительности.Type 2: Every version of dimension member represented with separate row in the table usually with columns that represent period of validity
  • Тип 3. Хранение ограниченного журнала для выбранных атрибутов с помощью дополнительных столбцов в той же строке.Type 3: Keeping limited history for selected attribute(s) using additional columns in the same row
  • Тип 4. Хранение журнала в отдельной таблице. При этом исходная таблица измерения поддерживает последние (текущие) версии элементов измерений.Type 4: Keeping history in the separate table while original dimension table keeps latest (current) dimension member versions

При выборе стратегии SCD за точное хранение таблиц измерений отвечает уровень ETL (извлечение — преобразование — загрузка), и для этого обычно требуется много кода и сложное обслуживание.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.

Чтобы значительно снизить сложность кода, можно использовать темпоральные таблицы с системным управлением версиями в SQL Server 2016, поскольку журнал данных сохраняется автоматически.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. Темпоральные таблицы в SQL Server 2016 наиболее близки к SCD типа 4, учитывая, что они реализуются с помощью двух таблиц.Given its implementation using two tables, temporal tables in SQL Server 2016 are closest to Type 4 SCD. Однако поскольку темпоральные запросы позволяют ссылаться только на текущую таблицу, можно также рассмотреть применение временных таблиц в средах, где планируется использовать SCD типа 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.

Для преобразования обычного измерения в SCD просто создайте новую или измените существующую таблицу, чтобы она стала темпоральной таблицей с системным управлением версиями.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. Если существующая таблица измерения содержит исторические данные, создайте отдельную таблицу, переместите в нее исторические данные и сохраните текущие (действующие) версии измерения в исходной таблице измерения.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. Затем с помощью синтаксиса ALTER TABLE преобразуйте таблицу измерения в темпоральную таблицу с системным управлением версиями с предопределенной таблицей журнала.Then use ALTER TABLE syntax to convert your dimension table to a system-versioned temporal table with a predefined history table.

В следующем примере показан этот процесс и предполагается, что таблица измерения DimLocation уже имеет столбцы ValidFrom и ValidTo с типом datetime2, не допускающие значения NULL, которые заполняются процессом ETL: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 history table to the DimLocation*/
ALTER TABLE DimLocation SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DimLocationHistory));

Для обслуживания SCD во время процесса загрузки хранилища данных после его создания не требуется никакой дополнительный код.No additional code is required to maintain SCD during the data warehouse loading process once you created it.

На следующем рисунке показано, как можно использовать темпоральные таблицы в простом сценарии с двумя SCD (DimLocation и DimProduct) и одной таблицей фактов.The following illustration shows how you can use Temporal Tables in a simple scenario involving 2 SCDs (DimLocation and DimProduct) and one fact table.

TemporalSCDTemporalSCD

Чтобы использовать показанные выше SCD в отчетах, необходимо эффективно настроить запросы.In order to use above SCDs in reports, you need to effectively adjust querying. Например, можно вычислить общий объем продаж и среднее количество проданных продуктов на человека за последние шесть месяцев.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. Обратите внимание, что для обеих метрик требуется корреляция важных для анализа данных из таблицы фактов и измерений, атрибуты которых могли измениться (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). Следующий запрос должным образом вычисляет требуемые метрики:The following 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 ;

РекомендацииConsiderations:

  • Использование темпоральных таблиц с системным управлением версиями для SCD допустимо, если срок действия, вычисленный на основе времени транзакции базы данных, согласуется с вашей бизнес-логикой.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. При загрузке данных со значительной задержкой время транзакции может быть неприемлемо.If you load data with significant delay, transaction time might not be acceptable.
  • По умолчанию темпоральные таблицы с системным управлением версиями не разрешают изменение исторических данных после загрузки (журнал можно изменить, установив значение OFF для параметра SYSTEM_VERSIONING).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). Это может быть ограничением в случаях, когда изменение исторических данных происходит регулярно.This might be limitation in cases where changing historical data happens regularly.
  • Темпоральные таблицы с системным управлением версиями формируют версию строки при любом изменении столбца.Temporal system-versioned tables generate row version on any column change. Если вы хотите запретить создание новых версий при изменении определенных столбцов, необходимо включить это ограничение в логику ETL.If you want to suppress new versions on certain column change you need to incorporate that limitation in the ETL logic.
  • Если ожидается значительное число исторических строк в таблицах SCD, рассмотрите возможность использования кластеризованного индекса columnstore в качестве основного хранилища для таблицы журнала.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. Это уменьшит место, занимаемое таблицей журнала, и ускорит аналитические запросы.That will reduce history table footprint and speed up your analytical queries.

Восстановление поврежденных данных на уровне строкRepairing row-level data corruption

Вы можете использовать исторические данные в темпоральных таблицах с системным управлением версиями, чтобы быстро восстанавливать отдельные строки в любое из ранее записанных состояний.You can rely on historical data in system-versioned temporal tables to quickly repair individual rows to any of the previously captured states. Это свойство темпоральных таблиц очень удобно использовать, когда можно найти затронутые строки или когда известно время нежелательного изменения данных. В этом случае вы можете выполнить восстановление очень эффективно без задействования резервных копий.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.

Такой подход имеет несколько преимуществ.This approach has several advantages:

  • Вы можете очень точно управлять областью восстановления.You are able to control the scope of the repair very precisely. Записи, которые не были затронуты, должны остаться в последнем состоянии, что часто является принципиальным требованием.Records that are not affected need to stay at the latest state, which is often a critical requirement.
  • Операция очень эффективна, и база данных остается доступна для всех рабочих нагрузок с использованием данных.Operation is very efficient and the database stays online for all workloads using the data.
  • Сама операция восстановления поддерживает управление версиями.The repair operation itself is versioned. Имеется журнал аудита для самой операции восстановления, так что при необходимости позже можно проанализировать произошедшее.You have audit trail for repair operation itself, so you can analyze what happened later if necessary.

Действие восстановления можно довольно легко автоматизировать.Repair action can be automated relatively easily. Ниже приведен пример кода хранимой процедуры, которая выполняет восстановление данных для таблицы Employee, используемой в сценарии аудита данных.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

Эта хранимая процедура принимает входные параметры @EmployeeID и @versionNumber.This stored procedure takes @EmployeeID and @versionNumber as input parameters. По умолчанию эта процедура восстанавливает состояние строки из журнала до последней версии (@versionNumber = 1).This procedure by default restores row state to the last version from the history (@versionNumber = 1).

На следующем рисунке показано состояние строки до и после вызова процедуры.The following picture shows state of the row before and after the procedure invocation. Красный прямоугольник отмечает текущую, неправильную версию строки, а зеленый прямоугольник отмечает правильную версию из журнала.Red rectangle marks current row version that is incorrect, while green rectangle marks correct version from the history.

TemporalUsageRepair1TemporalUsageRepair1

EXEC sp_RepairEmployeeRecord @EmployeeID = 1, @versionNumber = 1

TemporalUsageRepair2TemporalUsageRepair2

Можно определить эту хранимую процедуру восстановления таким образом, чтобы она принимала точное время вместо версии строки.This repair stored procedure can be defined to accept an exact timestamp instead of row version. Она будет восстанавливать строку до той версии, которая была активна на предоставленный момент времени (т. е. в состояние на момент времени).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

На следующем рисунке показан сценарий восстановления для тех же данных с условием времени.For the same data sample the following picture illustrates repair scenario with time condition. Здесь выделены параметр @asOf, выбранная строка в журнале, которая была действующей на указанный момент времени, и новая версия строки в текущей таблице после операции восстановления.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:

TemporalUsageRepair3TemporalUsageRepair3

Корректировка данных может стать частью автоматической загрузки данных в системах хранения данных и подготовки отчетов.Data correction can become part of automated data loading in data warehousing and reporting systems. Если только что обновленное значение неправильно, для устранения этого во многих сценариях достаточно восстановить предыдущую версию.If a newly updated value is not correct then, in many scenarios, restoring the previous version from history is good enough mitigation. На следующей схеме показано, как этот процесс можно автоматизировать.The following diagram shows how this process can be automated:

TemporalUsageRepair4TemporalUsageRepair4

Дальнейшие действияNext steps