SQL Server 2016: Temporal Tables (часть 1)

By | June 25, 2015

Temporal table были представлены как функционал базы данных в стандарте ANSI SQL:2011 и теперь будут поддерживаться в SQL Server 2016. Довольно часто бывают задачи, когда необходимо хранить журнал изменений. Теперь не нужно будет делать свои решения. Temporal table позволяют получить не только последние сохраненные данные, но данные на определенный момент времени.

Примечание: описанный ниже функционал действителен для версии SQL Server 2016 CTP 2.1
Примечание 2: т.к. пока нет устоявшегося перевода на русский язык (есть предложения по использованию терминов: темпоральные таблицы и таблицы с версионностью), то я буду использовать английское название

Temporal table создается также как и обычная таблица за исключением некоторых отличий.
Необходимо:

  • задать первичный ключ
  • создать две non-nullable колонки с типом данных DATETIME2, обозначающие начало и конец периода актуальности строки
  • начальную колонку периода пометить опцией GENERATED ALWAYS AS ROW START
  • вторую колонку периода пометить опцией GENERATED ALWAYS AS ROW END
  • указать какие колонки использовать для хранения периода актуальности с помощью опции PERIOD FOR SYSTEM_TIME (<startcol>, <endcol>)
  • задать связанную таблицу для хранения истории (SQL Server сам создаст её, если она ещё не существует)

Кстати, при необходимости вы можете обычную таблицу перевести в temporal table.

Давайте посмотрим скрипт, создающий temporal table.

CREATE TABLE dbo.Products
productid INT NOT NULL CONSTRAINT PK_Product PRIMARY KEY NONCLUSTERED,
productname VARCHAR(50NOT NULL,
StartDate DATETIME2(0) GENERATED ALWAYS AS ROW START NOT NULL,
EndDate DATETIME2(0) GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME (StartDate, EndDate)
)
WITH ( SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.ProductsHistory )
)

Примечание 3: если вы явно не укажите имя для исторической таблицы, то оно будет автоматически сформировано по шаблону MSSQL_TemporalHistoryFor_<object id>

После выполнения скрипта обратите внимание, что в SSMS эта таблица помечена как System-Versioned. Кроме того, появился новый узел для исторической таблицы: dbo.ProductsHistory (History).


2015-06-26_21-54-32

Следует отметить, что в исторической таблице создан кластерный индекс по колонкам: <primary key column>, <system period start column>, <system period end column>. Но он создается если историческая таблица была создана автоматически. Если же вы сами создали её, то позаботьтесь о создании этого индекса самостоятельно.

Давайте рассмотрим ограничения для temporal table.

  1. После создания temporal table вы не можете применять DDL команды ни к основной, ни к исторической таблицам. И нельзя удалять Temporal table.
  2. Нельзя изменять данные в исторической таблице

Второе ограничение вполне логично. Но как же быть с первым ограничением? Для изменения структуры temporal table необходимо выключить поддержку версионности.

ALTER TABLE dbo.Products SET SYSTEM_VERSIONING OFF )

Теперь в списке таблиц вы найдете две обычные таблицы: dbo.Products и dbo.ProductsHistory. Давайте добавим ещё одну колонку. Вносить изменение нужно в обе таблицы, если вы хотите повторно использовать dbo.ProductsHistory как историческую таблицу для dbo.Products

ALTER TABLE dbo.Products ADD price numeric(5, 2)
ALTER TABLE dbo.ProductsHistory ADD price numeric(5, 2)

И снова включаем поддержку версионности:

ALTER TABLE dbo.Products SET
( SYSTEM_VERSIONING = ON 
( HISTORY_TABLE = dbo.ProductsHistoryDATA_CONSISTENCY_CHECK OFF

А теперь рассмотрим, как работает изменение данных для таких таблиц.

Добавим пару новых строк и посмотрим на содержимое таблицы:

INSERT INTO dbo.Products(productid, productname, price)
VALUES (1‘Milk’, 30), (2, ‘Apple’, 50)
SELECT FROM dbo.Products

Как видите, поле StartDate заполнено значением времени в формате UTC когда выполнялся запрос, а значение поля EndDate значением по умолчанию – 9999-12-31 23:59:59. В последнем примере транзакция была объявлена неявно. Если же транзакцию задать явно, то в StartDate попадет время не выполнения команды на изменение данных, а время начала транзакции. Выполните скрипт:

BEGIN TRAN
SELECT ‘Transaction start time: ‘ CONVERT(CHAR(19), SYSUTCDATETIME(), 121)
INSERT INTO dbo.Products(productid, productname, priceVALUES (3‘Swits’, 100)
WAITFOR DELAY ’00:00:10′
INSERT INTO dbo.Products(productid, productname, priceVALUES (4‘Peanut’, 30)
SELECT ‘Transaction end time: ‘ CONVERT(CHAR(19), SYSUTCDATETIME(), 121)
COMMIT TRAN
SELECT FROM dbo.Products

Как видите время в StartDate для двух последних строк совпадает со временем начала транзакции.

Теперь посмотрим что хранится в исторической таблице:

SELECT FROM dbo.ProductsHistory

Она пуста. Что в общем-то и ожидалось, ведь мы ещё не меняли данные. Так давайте сделаем это.

UPDATE dbo.Products SET price = 35 WHERE productid=
WAITFOR DELAY ’00:00:10′
DELETE FROM dbo.Products WHERE productid IN (4)

При изменении данных в историческую таблицу попали две строки – одна для измененной строки, вторая для удаленной.

Теперь рассмотрим как получить данные актуальные на определенный момент времени, в заданный интервал и т.п. Для этого необходимо использовать выражение FOR SYSTEM_TIME с различными опциями, например:

SELECT FROM dbo.Products FOR SYSTEM_TIME AS OF ‘2015-06-25 12:18’

Доступны следующие опции:

Опция Накладываемые условия Результат
AS OF <date_time> SysStartTime <= date_time AND SysEndTime > date_time Возвращает набор строк актуальных на указанный момент времени. При необходимости автоматически происходит объединение исходной таблицы и исторической таблицы.
FROM <start_date_time> TO <end_date_time> SysStartTime < end_date_time AND SysEndTime > start_date_time Возвращает набор строк актуальных в заданный интервал времени. Обратите внимание что используется строгое неравенство. Если строка менялась несколько раз в течении этого интервала, то будут показаны все значения для этой строки.
BETWEEN <start_date_time> AND <end_date_time> SysStartTime <= end_date_time AND SysEndTime > start_date_time Похож на передыдущий вариант, но теперь указанное время включается в поисковый диапазон времени.
CONTAINED IN (<start_date_time> , <end_date_time>) SysStartTime >= start_date_time AND SysEndTime <= end_date_time Возвращает строки, которые изменялись в указанный период и при этом перестали быть актуальными в этот же период

На этом пока всё, продолжение следует.