Советы по использованию временных таблиц

В SQL Server 2016 были реализованы временные таблицы с системным управлением версиями, позволяющие отслеживать историю изменений данных. В предлагаемой статье я бы хотел дать читателям несколько рекомендаций по работе с временными таблицами, например в отношении представления значения столбца периода в целевом часовом поясе, представления вырожденных интервалов и оптимизации, используемой вложенным предложением CONTAINED IN.

Тестовые данные

В некоторых примерах статьи запрос направляется во временную таблицу тестовой базы данных WideWorld­Importers. Документацию по этой базе данных можно найти по адресу: go.microsoft.com/fwlink/? LinkID=800631. Чтобы установить тестовую базу данных на своем компьютере, загрузите файл резервной копии WideWorldImporters-Full.bak по адресу: go.microsoft.com/fwlink/? LinkID=800630. Предположим, вы поместили файл в папку C:\WWI\; восстановите базу данных, выполнив программный код листинга 1 (при необходимости замените исходный и целевой пути).

Представление столбцов периода в целевом часовом поясе

Напомню, что столбцы периода во временной таблице содержат время часового пояса в формате UTC как значения DATETIME2. Предположим, что их нужно представить как значения DATETIMEOFFSET в определенном часовом поясе. Рекомендуемый инструмент для таких преобразований — функция AT TIME ZONE, которая рассматривалась в статье «Тип данных DATETIMEOFFSET и функция AT TIME ZONE в SQL Server 2016» (опубликована в Windows IT Pro/RE № 3 2017 года). Очевидно, что это задача непростая. Для возвращения значения типа DATETIME2, сохраненного в исходном часовом поясе (в нашем случае UTC) как значения типа DATETIMEOFFSET с определенным целевым часовым поясом, необходимы два преобразования AT TIME ZONE: одно для преобразования значения без учета сдвига в значение со сдвигом с исходным часовым поясом, другое для переключения сдвига с исходного часового пояса на целевой. Такое преобразование демонстрирует программный код в листинге 2. Этот программный код формирует выходные данные, показанные на экране 1.

Экран 1. Выходные данные листинга 2

Сложность временных таблиц связана с текущими строками, хранящими максимально возможное значение типа в столбце конца периода (9999-12-31 23:59:59.9999999 при использовании максимальной точности). В качестве примера рассмотрите запрос, приведенный в листинге 3.

Этот запрос формирует выходные данные, показанные на экране 2. Здесь показаны четыре элемента, каждый с четырьмя вариантами. Текущий вариант отмечен максимальным значением в столбце ValidTo.

Экран 2. Выходные данные запроса листинга 3

Если нужно представить значения в целевом часовом поясе с отрицательным сдвигом, например тихоокеанское время США (зима), то местное время корректируется назад. При использовании максимального значения оно выражается в целевом часовом поясе: 9999-12-31 15:59:59.9999999 -08:00. Это показано в программным коде лис­тинга 4.

Этот программный код формирует выходные данные, показанные на экране 3.

Экран 3. Выходные данные запроса листинга 4

Если целевой часовой пояс имеет положительный сдвиг, например Russian Standard Time (московское время), то местное время максимального значения типа не поддается выражению в SQL Server, так как это год 10 000, а SQL Server может выражать только даты до года 9999. Не объявляя о неудачной попытке преобразования, SQL Server возвращает максимальное значение типа со смещением +00:00. Это демонстрирует программный код в листинге 5.

Этот программный код выдает выходные данные, показанные на экране 4.

Экран 4. Выходные данные запроса листинга 5

Если вас устраивает такой результат, то вам не следует предпринимать никаких дальнейших действий. Однако, если вы требуете устойчивого поведения, независимо от отрицательного или положительного сдвига целевого часового пояса, то следует возвратить максимальное значение типа со сдвигом +00:00. Это легко сделать с помощью выражения CASE, например как в листинге 6.

Этот программный код формирует выходные данные, показанные на экране 5.

Экран 5. Выходные данные запроса листинга 6

Затем опробуйте данный программный код с часовым поясом тихоокеанское время США (зима) (см. листинг 7).

Вы получите выходные данные, показанные на экране 6.

Экран 6. Выходные данные запроса листинга 7

Лично для меня это предпочтительный вариант.

Вырожденные интервалы

Когда вы изменяете данные во временной таблице в ходе явной транзакции, фактическим временем всех изменений является время начала транзакции. Это может привести к любопытной ситуации, если применить несколько обновлений к одной строке в одной транзакции. Исходный и последний варианты строки будут иметь отличные от нуля интервалы длительности, также известные как вырожденные интервалы. Чтобы продемонстрировать это, я воспользуюсь временной таблицей с именем Products. Выполните программный код листинга 8, чтобы создать таблицу Products и заполнить ее несколькими строками.

Выполните программный код из листинга 9, чтобы применить несколько обновлений в одной транзакции.

Самая старая версия строки для продукта 3 должна иметь P1 как значение validfrom и P2 как значение validto с ценой, равной 10.00. Самая новая, текущая версия должна иметь P2 как значение validfrom и maximum как значение validto с ценой, равной 13.00. Но были сформированы еще две версии со значениями validfrom и validto равными P2, с ценами 11.00 и 12.00. Если вы запрашиваете данные с использованием предложения FOR SYSTEM_TIME, то SQL Server отбрасывает вырожденные интервалы. Например, используйте программный код, приведенный в листинге 10, чтобы вернуть все версии строк для продукта 3 с использованием вложенного предложения ALL.

Этот программный код формирует выходные данные, исключая вырожденные интервалы (результаты приведены на экране 7).

Экран 7. Результаты запроса листинга 10

План выполнения для этого запроса показан на рисунке 1.

Рисунок 1. Вырожденные интервалы отброшены

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

Чтобы сохранить вырожденные интервалы, необходимо напрямую запросить текущую таблицу и таблицу журнала и объединить результаты (см. листинг 11).

Этот программный код формирует выходные данные, которые содержат вырожденные интервалы (см. экран 8).

Экран 8. Результаты запроса в листинге 11

План для этого запроса показан на рисунке 2.

Рисунок 2. Вырожденные интервалы включены

Как мы видим, в плане нет предикатов для удаления вырожденных интервалов.

При использовании вложенного предложения AS OF для возврата интервалов, которые были действительны в определенный момент времени, все вырожденные интервалы отбрасываются. Например, рассмотрим запрос, приведенный в листинге 12 (предполагается, что @datetime — входной параметр для процедуры или функции).

Этот запрос возвращает строки, в которых @datetime больше или равно validfrom и меньше, чем validto. Это означает, что вырожденные интервалы никогда не будут совпадать. Поэтому нет смысла составлять запросы непосредственно к текущей таблице и таблице журнала с логически эквивалентными предикатами. Однако при использовании остальных вложенных предложений (FROM TO, BETWEEN и CONTAINED IN) запрос не возвратит вырожденных интервалов, тогда как прямые запросы к текущей таблице и таблице журнала — возвратят. Примеры в листинге 13 показывают альтернативные варианты с прямыми запросами для каждого вложенного предложения.

Завершив проверку вариантов, выполните программный код листинга 14.

Оптимизированное вложенное предложение CONTAINED IN

Вложенное предложение CONTAINED IN возвращает все строки, период действия которых содержится во входном интервале, то есть строки с началом периода, превышающим или равным началу входного интервала, и концом интервала, меньшим или равным концу входного интервала. В SQL Server применяется специальная оптимизация для вложенного предложения CONTAINED IN: физическое сканирование текущей таблицы не выполняется, если конец входного интервала отличается от максимально возможного значения типа. Программе известно, что в этом случае строки из текущей таблицы не могут подойти. Поэтому, если нужно идентифицировать версии строк, содержащихся во входном периоде, лучше использовать вложенное предложение CONTAINED IN, а не вложенное предложение ALL с явным предикатом фильтра. Чтобы продемонстрировать это, запустите сначала следующий программный код, чтобы включить отчеты статистики ввода-вывода:

SET STATISTICS IO ON;

Выполните программный код листинга 15 с использованием вложенного приложения ALL, чтобы идентифицировать версии строк за период с 2010 по 2016 год.

План выполнения для этого запроса показан на рисунке 3.

Рисунок 3. Доступ к обеим таблицам ALL с использованием обеих таблиц

Обратите внимание на доступ как к текущей таблице, так и к таблице журнала. Значение счетчика выполнения — 1 для сканирования индексов на обеих таблицах. Также обратите внимание на выходные данные STATISTICS IO, в которых присутствуют операции чтения для обеих таблиц (см. экран 9).

Экран 9. Выходные данные STATISTICS IO

Затем попытайтесь выполнить ту же задачу с использованием вложенного предложения CONTAINED IN, как в листинге 16.

План для этого запроса показан на рисунке 4.

Рисунок 4. Доступ только к таблице журналов с использованием CONTAINED IN

Посмотрите на Startup Expression Predicate («Предикат выражения запуска»). Он проверяет, равен ли конец входного периода максимальному возможного типа; только после этого начинается просмотр текущей таблицы. В этом примере, поскольку предикат ложный, просмотр не выполняется. Обратите внимание, что в выходных данных STATISTICS IO нет сведений об операциях ввода-вывода с текущей таблицей (см. экран 10).

Экран 10. В выходных данных STATISTICS IO нет сведений об операциях ввода-вывода с текущей таблицей

Может возникнуть вопрос, почему не направить прямой запрос к таблице журнала и таким образом избежать физического контакта с текущей таблицей? Такая возможность существует. Но, во-первых, цель предложения FOR SYSTEM_TIME — упростить логику и избавиться от необходимости заботиться о базовом разделении текущей таблицы и таблицы журнала. Во-вторых, что, если входные данные являются параметрами и пользователь-исполнитель иногда может запросить максимальный конец входного интервала? Вам не придется проверять начальное условие с помощью инструкции IF. Оптимизатор сделает это за вас. В листинге 17 приведен пример с переменными.

План для этого запроса показан на рисунке 5.

Рисунок 5. CONTAINED IN с переменными

Обратите внимание, что даже при использовании переменных или параметров применяется та же самая логика запуска. Выходные данные STATISTICS IO указывают, что с текущей таблицей не выполнялось никаких операций ввода-вывода (см. экран 11).

Экран 11. Выходные данные STATISTICS IO при использовании переменных

Завершив работу, выполните следующий программный код, чтобы отключить отчеты о статистике ввода-вывода:

SET STATISTICS IO OFF;

Итак, в этой статье я дал несколько рекомендаций относительно временных таблиц. Мы рассмотрели, как добиться согласованного поведения, когда нужно представить столбцы периода в желательном целевом часовом поясе с использованием выражения CASE. Кроме того, теперь вы знаете, что такое вырожденные интервалы и как возвратить их, направив запрос напрямую в текущую таблицу и таблицу журнала. Наконец, в статье описана оптимизация, используемая для вложенного предложения CONTAINED IN в предложении FOR SYSTEM_TIME, и объяснено, почему предпочтительно использовать именно это предложение по сравнению с альтернативными вариантами.

RESTORE DATABASE WideWorldImporters
  FROM DISK = N'C:\WWI\WideWorldImporters-Full.bak'
  WITH
    FILE = 1,
    MOVE N'WWI_Primary'         TO N'C:\WWI\WideWorldImporters.mdf', 
    MOVE N'WWI_UserData'        TO N'C:\WWI\WideWorldImporters_UserData.ndf', 
    MOVE N'WWI_Log'             TO N'C:\WWI\WideWorldImporters.ldf', 
    MOVE N'WWI_InMemory_Data_1' TO N'C:\WWI\WideWorldImporters_InMemory_Data_1',
    REPLACE, NOUNLOAD, STATS = 5;

DECLARE
  @dt             AS DATETIME2 = '20170118 12:00:00.0000000',
  @sourcetimezone AS sysname   = 'UTC',
  @targettimezone AS sysname   = 'Russian Standard Time';

SELECT
  @dt AT TIME ZONE @sourcetimezone AT TIME ZONE @targettimezone AS targetvalue;

USE WideWorldImporters;

SELECT StockItemID, ValidFrom, ValidTo
FROM Warehouse.StockItems
  FOR SYSTEM_TIME ALL
WHERE StockItemName LIKE 'An%'
ORDER BY StockItemID, ValidFrom;

DECLARE
  @targettimezone AS sysname = 'Pacific Standard Time';

SELECT StockItemID,
  ValidFrom AT TIME ZONE N'UTC' AT TIME ZONE @targettimezone AS ValidFrom,
  ValidTo AT TIME ZONE N'UTC' AT TIME ZONE @targettimezone AS ValidTo
FROM Warehouse.StockItems
  FOR SYSTEM_TIME ALL
WHERE StockItemName LIKE 'An%'
ORDER BY StockItemID, ValidFrom;

DECLARE
  @targettimezone AS sysname = 'Russian Standard Time';
SELECT StockItemID,
  ValidFrom AT TIME ZONE N'UTC' AT TIME ZONE @targettimezone AS ValidFrom,
  ValidTo AT TIME ZONE N'UTC' AT TIME ZONE @targettimezone AS ValidTo
FROM Warehouse.StockItems
  FOR SYSTEM_TIME ALL
WHERE StockItemName LIKE 'An%'
ORDER BY StockItemID, ValidFrom;

DECLARE
  @targettimezone AS sysname = 'Russian Standard Time';
SELECT StockItemID,
  ValidFrom AT TIME ZONE N'UTC' AT TIME ZONE @targettimezone AS ValidFrom,
  ValidTo AT TIME ZONE
    CASE WHEN ValidTo = '99991231 23:59:59.9999999'
      THEN 'UTC'
      ELSE @targettimezone
    END AS ValidTo
FROM Warehouse.StockItems
  FOR SYSTEM_TIME ALL
WHERE StockItemName LIKE 'An%'
ORDER BY StockItemID, ValidFrom;

DECLARE
  @targettimezone AS sysname = 'Pacific Standard Time';
SELECT StockItemID,
  ValidFrom AT TIME ZONE N'UTC' AT TIME ZONE @targettimezone AS ValidFrom,
  ValidTo AT TIME ZONE
    CASE WHEN ValidTo = '99991231 23:59:59.9999999'
      THEN 'UTC'
      ELSE @targettimezone
    END AS ValidTo
FROM Warehouse.StockItems
  FOR SYSTEM_TIME ALL
WHERE StockItemName LIKE 'An%'
ORDER BY StockItemID, ValidFrom;

-- Создать и заполнить таблицу Products
USE tempdb;
IF OBJECT_ID(N'dbo.Products', N'U') IS NOT NULL
BEGIN
  IF OBJECTPROPERTY(OBJECT_ID(N'dbo.Products', N'U'), N'TableTemporalType') = 2
    ALTER TABLE dbo.Products SET ( SYSTEM_VERSIONING = OFF );
  DROP TABLE IF EXISTS dbo.ProductsHistory, dbo.Products;
END;
GO

CREATE TABLE dbo.Products
(
  productid   INT          NOT NULL
    CONSTRAINT PK_dboProducts PRIMARY KEY(productid),
  productname NVARCHAR(40) NOT NULL,
  supplierid  INT          NOT NULL,
  categoryid  INT          NOT NULL,
  unitprice   MONEY        NOT NULL,
  validfrom   DATETIME2
    GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
  validto     DATETIME2
    GENERATED ALWAYS AS ROW END   HIDDEN NOT NULL,
  PERIOD FOR SYSTEM_TIME (validfrom, validto)
)
WITH ( SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.ProductsHistory ) );

-- Вставить строки в точке времени P1
INSERT INTO dbo.Products(productid, productname, supplierid, categoryid, unitprice)
  VALUES(1, 'Product HHYDP', 1, 1, 18.00),
        (2, 'Product RECZE', 1, 1, 19.00),
        (3, 'Product IMEHJ', 1, 2, 10.00),
        (4, 'Product KSBRM', 2, 2, 22.00),
        (5, 'Product EPEIM', 2, 2, 21.35);

BEGIN TRAN; -- точка времени P2

WAITFOR DELAY '00:00:01';

-- точка времени P3
UPDATE dbo.Products
  SET unitprice += 1
WHERE productid = 3;

WAITFOR DELAY ‘00:00:01’;

-- точка времени P4
UPDATE dbo.Products
  SET unitprice += 1
WHERE productid = 3;

WAITFOR DELAY '00:00:01';

-- точка времени P5
UPDATE dbo.Products
  SET unitprice += 1
WHERE productid = 3;

COMMIT TRAN;

SELECT productid, unitprice, validfrom, validto
FROM dbo.Products
  FOR SYSTEM_TIME ALL
WHERE productid = 3
ORDER BY validfrom;

SELECT productid, unitprice, validfrom, validto
FROM dbo.Products
WHERE productid = 3

UNION ALL
SELECT productid, unitprice, validfrom, validto
FROM dbo.ProductsHistory
WHERE productid = 3

ORDER BY validfrom;

SELECT productid, unitprice, validfrom, validto
FROM dbo.Products
  FOR SYSTEM_TIME AS OF @datetime;

Пример с FROM TO, который исключает вырожденные интервалы:
SELECT productid, unitprice, validfrom, validto
FROM dbo.Products FOR SYSTEM_TIME FROM @start TO @end;
Альтернативный вариант с прямыми запросами, включающий вырожденные интервалы:
SELECT productid, unitprice, validfrom, validto
FROM dbo.Products
WHERE validfrom < @end AND validto > @start
UNION ALL
SELECT productid, unitprice, validfrom, validto
FROM dbo.ProductsHistory
WHERE validfrom < @end AND validto > @start;
Пример с BETWEEN, который исключает вырожденные интервалы:
SELECT productid, unitprice, validfrom, validto
FROM dbo.Products FOR SYSTEM_TIME BETWEEN @start AND @end;
Альтернативный вариант с прямыми запросами, включающий вырожденные интервалы:
SELECT productid, unitprice, validfrom, validto
FROM dbo.Products
WHERE validfrom <= @end AND validto > @start
UNION ALL
SELECT productid, unitprice, validfrom, validto
FROM dbo.ProductsHistory
WHERE validfrom <= @end AND validto > @start;
Пример с CONTAINED IN, который исключает вырожденные интервалы:
SELECT productid, unitprice, validfrom, validto
FROM dbo.Products FOR SYSTEM_TIME CONTAINED IN (@start, @end);
Альтернативный вариант с прямыми запросами, включающий вырожденные интервалы:
SELECT productid, unitprice, validfrom, validto
FROM dbo.Products
WHERE validfrom >= @start AND validto <= @end
UNION ALL
SELECT productid, unitprice, validfrom, validto
FROM dbo.ProductsHistory
WHERE validfrom >= @start AND validto <= @end;

IF OBJECT_ID(N'dbo.Products', N'U') IS NOT NULL
BEGIN
  IF OBJECTPROPERTY(OBJECT_ID(N'dbo.Products', N'U'), N'TableTemporalType') = 2
    ALTER TABLE dbo.Products SET ( SYSTEM_VERSIONING = OFF );
  DROP TABLE IF EXISTS dbo.ProductsHistory, dbo.Products;
END;

USE WideWorldImporters;

SELECT StockItemID, ValidFrom, ValidTo
FROM Warehouse.StockItems
  FOR SYSTEM_TIME ALL
WHERE StockItemName LIKE 'An%'
  AND ValidFrom >= '20100101 00:00:00.0000000'
  AND ValidTo <= '20161231 23:59:59.9999999';

SELECT StockItemID, ValidFrom, ValidTo
FROM Warehouse.StockItems
  FOR SYSTEM_TIME CONTAINED IN
    ('20100101 00:00:00.0000000', '20161231 23:59:59.9999999')
WHERE StockItemName LIKE 'An%';

DECLARE
  @start AS DATETIME2 = '20100101 00:00:00.0000000',
  @end AS DATETIME2 = '20161231 23:59:59.9999999';

SELECT StockItemID, ValidFrom, ValidTo
FROM Warehouse.StockItems
  FOR SYSTEM_TIME CONTAINED IN (@start, @end)
WHERE StockItemName LIKE 'An%';