Мы продолжаем изучение реализованного в версии SQL Server 2016 нового средства — временных таблиц, в частности кода и демонстрационных данных. Это завершающая статья серии, а первая («Cоздание временных таблиц и модификация данных») была опубликована в предыдущем номере журнала. Таблицы с управлением версиями — это новое средство, реализованное в системе Microsoft SQL Server 2016. В первой части речь шла о том, что представляют собой такие таблицы, как они создаются и как вносятся изменения в данные, которые хранятся в подобных таблицах. В данной статье основное внимание уделяется организации запросов к данным, а также соображениям, касающимся оптимизации.

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

Если вы хотите выполнять описываемые в настоящей статье запросы и получать те же результаты, что и в моих примерах, вам нужно будет включить в свои таблицы соответствующие демонстрационные данные. С помощью кода, приведенного в листинге 1, создайте текущую таблицу и таблицу истории изменений (Employees и EmployeesHistory) и введите в них демонстрационные данные.

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

SELECT *
FROM dbo.Employees;

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

 

Содержимое таблицы Employees
Экран 1. Содержимое таблицы Employees

Запустите следующий код для отображения содержимого таблицы истории изменений:

SELECT *
FROM dbo.EmployeesHistory;

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

 

Содержимое таблицы EmployeesHistory
Экран 2. Содержимое таблицы EmployeesHistory

Теперь в ваших таблицах содержатся те же демонстрационные данные, что и в моих, и мы можем приступить к подготовке запросов. И здесь я прошу иметь в виду следующее: выполняя примеры по модификации данных, приведенные в первой статье серии, я уложился в короткий промежуток времени (буквально в пару часов). Именно поэтому упомянутые примеры содержат сведения за столь небольшой период. В реальных условиях подобные изменения обычно происходят на протяжении нескольких лет.

Соображения по запросам к данным и оптимизации

Содержащиеся во временной таблице сведения хранятся в двух таблицах (в текущей и истории изменений), но приложение может и не иметь сведений об этом. Приложение должно взаимодействовать только с одной таблицей. Если ваш запрос касается лишь текущих данных, вам следует опрашивать только текущую таблицу и не использовать специальные операторы. Предположим, что вы хотите получить сведения о служащих на текущий момент. Выполните следующий запрос:

SELECT *
FROM dbo.Employees;

План этого запроса показан на рисунке 1. Он предполагает просмотр кластеризованного индекса текущей таблицы; разумеется, проверка таблицы истории изменений при этом не предусматривается.

 

План запроса, не содержащего FOR SYSTEM_TIME
Рисунок 1. План запроса, не содержащего FOR SYSTEM_TIME

А теперь предположим, что вам нужно получить сведения о служащих по состоянию на некий момент в прошлом, определяемый параметром @datetime, или даже за период, определяемый входными данными @start и @end. В принципе вы можете сформировать два запроса: один к текущей таблице, а второй — к истории изменений с соответствующими предикатами фильтров, которые позволили бы получить нужные версии, и объединить результаты с помощью оператора UNION ALL. Но с другой стороны, у вас есть возможность использовать оператор FOR SYSTEM_TIME, который указывается в запросе к временной таблице или к представлению на основе временной таблицы; в данном операторе вы определяете интересующее вас время или период действия. После этого SQL Server в фоновом режиме преобразует ваш короткий запрос в более сложные запросы к текущей таблице и таблице истории изменений.

Перед тем как приступить к разговору об операторе FOR SYSTEM_TIME, я хочу убедиться, что у вас имеется ясное представление об интервале, определяемом столбцами начала и окончания системного периода. Представляемые ими интервалы в математике именуются закрыто-открытыми и представляются как [sysstart, sysend). Квадратная скобка означает закрытую границу интервала (инклюзивную), а круглая — открытую границу (эксклюзивную). Так, в рассматриваемом нами случае значение sysstart включается в интервал, а значение sysend — не включается.

Оператор FOR SYSTEM_TIME указывается непосредственно после имени таблицы или представления и перед псевдонимом таблицы, как показано в следующем фрагменте кода:

SELECT …FROM 
FOR SYSTEM_TIME 
AS ;

Подвыражение, которое вы будете использовать чаще всего, — это AS OF. Литералы даты и времени, переменные или параметры передаются в качестве входных данных следующим образом: FOR SYSTEM_TIME AS OF @datetime. Система возвращает строки, которые считались действительными в момент ввода данных с учетом того обстоятельства, что столбцы периода представляют закрыто-открытый интервал. В нашей временной таблице Employees будут возвращены строки, отвечающие следующим предикатам: sysstart <= @datetime AND sysend > @datetime. К примеру, запрос листинга 2 возвращает строки с информацией о служащих, действительной по состоянию на 2015-06-01 20:11:01.

Перед выполнением запроса вы можете взглянуть на экраны 1 и 2 и попытаться определить, какие строки будут возвращены.

Как я уже отмечал, вы можете добиться того же результата с помощью двух запросов и оператора UNION ALL (листинг 3).

Смысл, да и план запроса остаются такими же, как показано на рисунке 2.

 

План запроса с использованием оператора  FOR SYSTEM_TIME AS OF @datetime
Рисунок 2. План запроса с использованием оператора FOR SYSTEM_TIME AS OF @datetime

Код (или решение) генерирует выходные данные, показанные на экране 3 (посмотрите, правильно ли вы назвали строки, которые должны быть возвращены).

 

Результаты запроса информации о сотрудниках
Экран 3. Результаты запроса информации о сотрудниках

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

Замечу, что следующие соображения применимы лишь в тех случаях, когда используются построчные индексы.

Возможно, у вас возникает вопрос: почему план предусматривает не поиск, а сканирование индексов? Дело в том, что, как вы, вероятно, помните, в качестве ведущего ключа текущих кластеризованных индексов таблиц выступает столбец empid, за которым следуют столбцы systart и sysend, а в нашем запросе фильтрация по столбцу empid не производится. Рассмотрим пример, в котором с помощью фильтра отбираются сведения о конкретном сотруднике (листинг 4). План данного запроса показан на рисунке 3.

 

План с процедурами поиска
Рисунок 3. План с процедурами поиска

На этот раз план включает в себя процедуры поиска в индексах. Если вы предполагаете, что в дальнейшем вам придется часто формировать запросы, не предусматривающие фильтрацию конкретного значения первичного ключа, то вполне вероятно, вы захотите также создать индекс столбцов системных периодов без столбца с ведущим первичным ключом.

Говоря о проблемах индексирования, следует отметить еще одно важное обстоятельство. При работе с предикатами нескольких диапазонов только один из них может использоваться в качестве предиката поиска (см. свойство Seek Predicates в плане); остальные используются в качестве остаточных предикатов (см. свойство Predicate в плане). Это означает, что в целях достижения оптимальной производительности (с тем чтобы сканировать меньшее число страниц в листе индексов) целесообразно формировать список ключей индекса следующим образом: (, , ). Если говорить о текущей таблице, то размещать столбец sysstart перед столбцом sysend в списке ключей вполне логично, поскольку столбец sysstart будет отображаться в чаще выбираемом предикате диапазона (все строки будут соответствовать критерию predicate sysend > @datetime, тогда как критерию sysstart <= @datetime будет соответствовать лишь некоторое подмножество строк). Но когда речь заходит о таблице истории изменений, картина меняется. Если вы обычно обращаете свои запросы к свежим данным, предикат диапазона на базе столбца sysend будет, как правило, выбираться чаще, чем предикат на базе столбца sysstart. Следовательно, при проведении тестов производительности обязательно попробуйте применить индекс, где в списке ключей sysend располагается перед sysstart. Это в первую очередь касается ситуаций, в которых, согласно вашим предположениям, каждая строка будет содержать множество версий.

Если вам требуется более подробная информация по индексированию, имейте в виду, что я подробно освещаю эту тему в статье «Советы по оптимизации для нескольких предикатов принадлежности диапазону» (части 1 и 2 этой статьи опубликованы соответственно в «Windows IT Pro/RE» № 9 и № 10 за 2014 год).

Подвыражения FOR SYSTEM_TIME

Подвыражение AS OF — всего лишь одно из четырех подвыражений, поддерживаемых оператором FOR SYSTEM_TIME. Вот полный список этих поддерживаемых подвыражений:

  • AS OF @datetime
  • FROM @start TO @end
  • BETWEEN @start AND @end
  • CONTAINED IN (@start, @end)

В качестве входных данных здесь могут использоваться литералы, переменные или параметры. Первые три подвыражения являются стандартными, а четвертое — это разработанное специалистами Microsoft дополнение к стандарту.

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

 

Строки, удовлетворяющие требованиям подвыражений FOR SYSTEM_TIME

На рисунке 4 вы видите иллюстрации, графически представляющие строки, которые удовлетворяют требованиям различных подвыражений.

 

Иллюстрации подвыражений FOR SYSTEM_TIME
Рисунок 4. Иллюстрации подвыражений FOR SYSTEM_TIME

Блоки зеленого цвета представляют входные значения даты и времени в операторах FOR SYSTEM_TIME на основе точности представления данных соответствующим типом. У нас в столбцах системных периодов и во входных переменных используется тип DATETIME2 (0); таким образом, данные отображаются с точностью до одной секунды. В иллюстрации к AS OF зеленый блок представляет входное значение @datetime. В иллюстрациях к остальным подвыражениям зеленый блок крайний слева представляет входное значение @start, а крайний справа представляет входное значение @end.

Разумеется, между значениями @start и @end могут располагаться дополнительные поддерживаемые значения; они представлены блоками зеленого цвета, расположенными посередине. Я считаю, что использование цветных блоков, которые представляют поддерживаемые определенным типом значения, заметно облегчает понимание того, относится или не относится к типу то или иное значение. Красные и голубые стрелки показывают, какие диапазоны значений в столбцах systart и sysend соответственно позволяют считать строку отвечающей требованиям. Кстати, не забывайте о том, что существует неявно подразумеваемое (и принудительно применяемое системой SQL Server) условие, согласно которому sysend >= systart.

Давайте рассмотрим примеры для всех четырех случаев. Начнем с подвыражения AS OF @datetime. Следующий запрос возвращает строку для интересующего нас сотрудника. Идентификатор сотрудника хранится в @empid, которое было действительно на момент, определяемый значением даты и времени, хранящимся в @datetime (строка удовлетворяет требованиям: sysstart <= @datetime AND sysend > @datetime), как показано в листинге 5.

На экране 4 представлены выходные данные, которые вы получите для значения @datetime = '2015-06-01 20:11:01'.

 

Выходные данные для значения @datetime = ‘2015-06-01 20:11:01’
Экран 4. Выходные данные для значения @datetime = ‘2015-06-01 20:11:01’

А на экране 5 показаны выходные данные для значения @datetime = '2015-06-01 20:11:00'.

 

Выходные данные для значения @datetime = ‘2015-06-01 20:11:00’
Экран 5. Выходные данные для значения @datetime = ‘2015-06-01 20:11:00’

Подвыражение FOR SYSTEM_TIME FROM @start TO @end содержит все версии строки, где sysstart располагается перед входными данными @end, а sysend — после входных данных @start (требованиям удовлетворяют строки, где sysstart < @end AND sysend > @start). Рассмотрим пример, приведенный в листинге 6.

Этот запрос эквивалентен запросу в листинге 7.

Мы получаем следующие выходные данные, отображающие две версии строки для одного и того же служащего на протяжении интересующего нас периода (экран 6).

 

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

Подвыражение FOR SYSTEM_TIME BETWEEN @start AND @end напоминает предыдущее с той лишь разницей, что значение предиката к systart здесь меньше или равно входным данным @end (а не просто «меньше, чем»). Требованиям удовлетворяют строки, соответствующие сочетанию предикатов: sysstart <= @end AND sysend > @start. В листинге 8 приведен пример с этим подвыражением.

Данный запрос эквивалентен коду в листинге 9.

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

 

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

И наконец, четвертое подвыражение — FOR SYSTEM_TIME CONTAINED IN (@start, @end). В отличие от всех ранее рассмотренных, оно представляет собой расширение стандарта, предложенное специалистами Microsoft. В данном случае удовлетворяющими требованиям считаются такие строки, где значение sysstart совпадает или следует за входным значением @start, а значение sysend совпадает или располагается перед входным значением @end (требованиям удовлетворяют строки, в которых sysstart >= @start AND sysend <= @end). Иными словами, системный период должен содержаться внутри входного периода. В листинге 10 показан пример, иллюстрирующий использование данного подвыражения.

Данный запрос эквивалентен коду в листинге 11.

Этот запрос генерирует следующие выходные данные, демонстрируя две удовлетворяющие требованиям версии строки со сведениями, которые касаются указанного во входных данных сотрудника (экран 8).

 

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

Запросы к табличным выражениям

Как уже отмечалось, оператор FOR SYSTEM_TIME можно применять при работе с временными таблицами с управлением версиями и с представлениями. SQL Server распространит это действие на внутренние временные таблицы с управлением версиями. Что же касается версии SQL Server 2016 CTP2 (которая может измениться позднее), то SQL Server не поддерживает применение этого оператора к другим типам табличных выражений (производным таблицам, обобщенным табличным выражениям и встраиваемым функциям с табличными значениями). При обработке перечисленных объектов вам придется указывать оператор во внутренних ссылках на временные таблицы.

К примеру, предположим, что вы создаете встраиваемую функцию, возвращающую табличное значение (table-valued function, TVF) с именем ShowHierarchy и что функция эта, используя рекурсивное обобщенное табличное выражение, возвращает иерархию сотрудников с маршрутами к вышестоящим и индикацией уровня. Вы хотите иметь возможность обратиться к функции с запросом, чтобы она зафиксировала иерархию по состоянию на заданный момент времени таким образом, как показано в листинге 12.

Но система SQL Server пока не позволяет применять оператор FOR SYSTEM_TIME к встраиваемым функциям TVF и распространять его на базовые таблицы, как это делается в отношении представлений. Пока мы можем только применить следующий обходной маневр: передать значение даты и времени как входные данные для функции и указать его в качестве входных данных для оператора FOR SYSTEM_TIME AS OF непосредственно во внутренних запросах к таблице Employees. В листинге 13 приведен пример такого решения, определяющий функцию с именем ShowHierarchyAt.

Используем функцию в запросе листинга 14, передав в качестве входных данных значение даты и времени 2015-06-01 20:01:41. Получим следующее состояние иерархии, в котором она была в указанный момент времени (экран 9).

 

Состояние иерархии для 2015-06-01 20:01:41
Экран 9. Состояние иерархии для 2015-06-01 20:01:41

Посмотрим иерархию по состоянию на 2015-06-01 20:11:01 (листинг 15). Получим выходные данные, приведенные на экране 10.

 

Состояние иерархии на 2015-06-01 20:11:01
Экран 10. Состояние иерархии на 2015-06-01 20:11:01

Посмотрим иерархию по состоянию на 2015-06-01 21:32:20 (листинг 16). Получим выходные данные, как на экране 11.

 

Состояние иерархии на 2015-06-01 21:32:20
Экран 11. Состояние иерархии на 2015-06-01 21:32:20

Еще одна не реализованная пока возможность — указывать прямую корреляцию в операторе FOR SYSTEM_TIME в качестве входных данных. Эта возможность была бы полезной в случаях, когда пользователь хочет возвратить несколько состояний данных с помощью оператора APPLY (или коррелированного подзапроса) на базе входных значений даты и времени, хранящихся в таблице, или на базе параметра с табличным значением (table valued parameter, TVP). В листинге 17 показан пример, демонстрирующий это. Не пытайтесь выполнить данный код на практике, пока что система не позволяет это сделать.

Пока мы можем воспользоваться довольно простым обходным приемом. Создайте встраиваемую функцию TVF, которая в качестве входного параметра принимает значение даты и времени, опрашивает временную таблицу и формирует входной параметр для оператора FOR SYSTEM_TIME, как показано в листинге 18.

Далее, вместо того чтобы применять запрос с корреляцией, как ранее, табличного выражения, используйте встраиваемую функцию TVF и передайте столбец даты и времени из TVP в качестве входных данных для этой функции таким образом, как показано в листинге 19.

При выполнении этого кода генерируются выходные данные, как на экране 12.

 

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

Что называется, шутки ради приведу код, показывающий, как эти данные вводятся в одну строку (листинг 20). Этот код генерирует выходные данные на экране 13.

 

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

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

С момента появления новой функции прошло совсем немного времени, и статистических данных, касающихся производительности, собрано пока мало. И хотя Microsoft провозглашает некоторые общие принципы относительно того, как следует проводить индексирование, на сегодня мы не имеем специализированных индексов, разработанных для временных данных. Конечно, нужно будет учитывать, сколько версий вы хотите сохранять для одной строки и как будут выглядеть ваши запросы. Но можно предположить, что для работы с новыми таблицами вполне сгодятся универсальные индексы хранения данных columnstore и rowstore. К примеру, исключение сегмента columnstore, возможно, позволит пользователям довольно эффективно отсеивать данные, не представляющие для них интереса. Как бы то ни было, со временем опыта у нас будет больше.

Другим небольшим улучшением, которое мы увидим в будущем, станет возможность определять столбцы системного периода как полностью скрытые. В системе DB2, например, такая возможность уже поддерживается. Идея состоит в том, что при запуске SELECT* по временной таблице эти столбцы не будут возвращаться, пока на них не будет сделано явной ссылки. Я также могу упомянуть и некоторые другие небольшие возможности, которые пропущены, например связанные с табличными выражениями и взаимосвязями. Не будем жалеть о «полупустом стакане» и выражать недовольство предпринятыми разработчиком усилиями. Меня уже очень радуют первоначальные результаты изменений в SQL Server, относящиеся к такой важной области, как временные таблицы.

Листинг 1. Подготовка демонстрационных данных
-- Создайте базу данных TemporalDB и удалите таблицы, если они существуют
SET NOCOUNT ON;
IF DB_ID(N'TemporalDB') IS NULL CREATE DATABASE TemporalDB;
GO
USE TemporalDB;
GO
IF OBJECT_ID(N'dbo.Employees', N'U') IS NOT NULL
BEGIN
  IF OBJECTPROPERTY(OBJECT_ID(N'dbo.Employees', N'U'), N'TableTemporalType') = 2
    ALTER TABLE dbo.Employees SET ( SYSTEM_VERSIONING = OFF );
  IF OBJECT_ID(N'dbo.EmployeesHistory', N'U') IS NOT NULL
    DROP TABLE dbo.EmployeesHistory;
  DROP TABLE dbo.Employees;
END;
GO
-- Сформируйте и заполните таблицу Employees
CREATE TABLE dbo.Employees
(
  empid INT NOT NULL
    CONSTRAINT PK_Employees PRIMARY KEY NONCLUSTERED,
  mgrid INT NULL
    CONSTRAINT FK_Employees_mgr_emp REFERENCES dbo.Employees,
  empname VARCHAR(25) NOT NULL,
  sysstart DATETIME2(0) NOT NULL,
  sysend DATETIME2(0) NOT NULL
);
CREATE UNIQUE CLUSTERED INDEX ix_Employees
  ON dbo.Employees(empid, sysstart, sysend);
INSERT INTO dbo.Employees(empid, mgrid, empname, sysstart, sysend) VALUES
  (1 , NULL, 'David'  , '2015-06-01 19:54:04', '9999-12-31 23:59:59'),
  (2 , 1   , ‘Eitan’  , ‘2015-06-01 19:54:04’, ‘9999-12-31 23:59:59’),
  (3 , 1   , ‘Ina’    , ‘2015-06-01 20:01:41’, ‘9999-12-31 23:59:59’),
  (4 , 2   , ‘Seraph’ , ‘2015-06-01 19:54:20’, ‘9999-12-31 23:59:59’),
  (5 , 2   , ‘Jiru’   , ‘2015-06-01 19:54:20’, ‘9999-12-31 23:59:59’),
  (6 , 3   , ‘Steve’  , ‘2015-06-01 21:32:20’, ‘9999-12-31 23:59:59’),
  (7 , 4   , ‘Aaron’  , ‘2015-06-01 21:32:20’, ‘9999-12-31 23:59:59’),
  (8 , 5   , ‘Lilach’ , ‘2015-06-01 20:01:41’, ‘9999-12-31 23:59:59’),
  (9 , 4   , ‘Rita’   , ‘2015-06-01 21:32:20’, ‘9999-12-31 23:59:59’),
  (10, 5   , ‘Sean’   , ‘2015-06-01 20:01:41’, ‘9999-12-31 23:59:59’),
  (11, 6   , ‘Gabriel’, ‘2015-06-01 21:32:20’, ‘9999-12-31 23:59:59’);
-- Сформируйте и заполните таблицу EmployeesHistory
CREATE TABLE dbo.EmployeesHistory
(
  empid INT NOT NULL,
  mgrid INT NULL,
  empname VARCHAR(25) NOT NULL,
  sysstart DATETIME2(0) NOT NULL,
  sysend DATETIME2(0) NOT NULL
);
CREATE CLUSTERED INDEX ix_EmployeesHistory
  ON dbo.EmployeesHistory(empid, sysstart, sysend)
  WITH (DATA_COMPRESSION = PAGE);
INSERT INTO dbo.EmployeesHistory(empid, mgrid, empname, sysstart, sysend) VALUES
(6 , 2, ‘Steve’  , ‘2015-06-01 19:54:20’, ‘2015-06-01 21:32:20’),
(7 , 3, ‘Aaron’  , ‘2015-06-01 20:01:41’, ‘2015-06-01 21:32:20’),
(9 , 7, ‘Rita’   , ‘2015-06-01 20:01:41’, ‘2015-06-01 20:11:01’),
(9 , 3, ‘Rita’   , ‘2015-06-01 20:11:01’, ‘2015-06-01 21:32:20’),
(11, 7, ‘Gabriel’, ‘2015-06-01 20:01:41’, ‘2015-06-01 20:11:01’),
(11, 3, ‘Gabriel’, ‘2015-06-01 20:11:01’, ‘2015-06-01 21:32:20’),
(12, 9, ‘Emilia’ , ‘2015-06-01 20:01:41’, ‘2015-06-01 21:32:20’),
(13, 9, ‘Michael’, ‘2015-06-01 20:01:41’, ‘2015-06-01 20:11:01’),
(14, 9, ‘Didi’   , ‘2015-06-01 20:01:41’, ‘2015-06-01 20:11:01’);
-- Активируйте функцию system versioning
ALTER TABLE dbo.Employees ADD
  PERIOD FOR SYSTEM_TIME (sysstart, sysend);
ALTER TABLE dbo.Employees
  SET ( SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.EmployeesHistory ) );
Листинг 2. Запрос информации о сотрудниках
DECLARE @datetime AS DATETIME2(0) = ‘2015-06-01 20:11:01’;

SELECT *
FROM dbo.Employees FOR SYSTEM_TIME AS OF @datetime;
Листинг 3. Альтернативный способ запроса информации о сотрудниках
<Здесь объявляются переменные и назначения>

SELECT *
FROM dbo.Employees
WHERE sysstart <= @datetime
  AND sysend > @datetime
UNION ALL

SELECT *
FROM dbo.EmployeesHistory
WHERE sysstart <= @datetime
  AND sysend > @datetime;
Листинг 4. Отбор сведений о конкретном сотруднике
DECLARE @datetime AS DATETIME2(0) = ‘2015-06-01 20:11:01’, @empid AS INT = 9;

SELECT *
FROM dbo.Employees FOR SYSTEM_TIME AS OF @datetime
WHERE empid = @empid;
Листинг 5. Запрос для конкретного сотрудника
DECLARE @datetime AS DATETIME2(0) = ‘2015-06-01 20:11:01’, @empid AS INT = 9;
-- попробуйте также применить значение @datetime = ‘2015-06-01 20:11:00’
SELECT *
FROM dbo.Employees FOR SYSTEM_TIME AS OF @datetime
WHERE empid = @empid;
DECLARE @datetime AS DATETIME2(0) = ‘2015-06-01 20:11:01’, @empid AS INT = 9;

-- попробуйте также применить значение @datetime = ‘2015-06-01 20:11:00’
SELECT *
FROM dbo.Employees FOR SYSTEM_TIME AS OF @datetime
WHERE empid = @empid;
                             
<Здесь объявляются переменные и осуществляется назначение>
SELECT *
FROM dbo.Employees
WHERE empid = @empid
  AND sysstart <= @datetime
  AND sysend > @datetime
UNION ALL
SELECT *
FROM dbo.EmployeesHistory
WHERE empid = @empid
  AND sysstart <= @datetime
  AND sysend > @datetime;
Листинг 6. Пример запроса
DECLARE
  @start AS DATETIME2(0) = '2015-06-01 19:00:00',
  @end AS DATETIME2(0) = '2015-06-01 21:32:20',
  @empid AS INT = 9;

SELECT *
FROM dbo.Employees FOR SYSTEM_TIME FROM @start TO @end
WHERE empid = @empid;
Листинг 7. Альтернативный листингу 6 запрос
<Здесь объявляются переменные и осуществляется назначение>

SELECT *
FROM dbo.Employees
WHERE empid = @empid
  AND sysstart < @end
  AND sysend > @start

UNION ALL

SELECT *
FROM dbo.EmployeesHistory
WHERE empid = @empid
  AND sysstart < @end
  AND sysend > @start;
Листинг 8. Запрос, где значение предиката к systart меньше или равно входным данным @end
DECLARE
  @start AS DATETIME2(0) = '2015-06-01 19:00:00',
  @end AS DATETIME2(0) = '2015-06-01 21:32:20',
  @empid AS INT = 9;

SELECT *
FROM dbo.Employees FOR SYSTEM_TIME BETWEEN @start AND @end
WHERE empid = @empid;
Листинг 9. Код, альтернативный запросу листинга 8
<Здесь определяются переменные и осуществляется назначение>

SELECT *
FROM dbo.Employees
WHERE empid = @empid
  AND sysstart <= @end
  AND sysend > @start

UNION ALL

SELECT *
FROM dbo.EmployeesHistory
WHERE empid = @empid
  AND sysstart <= @end
  AND sysend > @start;
Листинг 10. Пример использования FOR SYSTEM_TIME CONTAINED IN(@start, @end)
DECLARE
  @start AS DATETIME2(0) = '2015-06-01 20:01:41',
  @end AS DATETIME2(0) = '2015-06-01 21:32:20',
  @empid AS INT = 9;

SELECT *
FROM dbo.Employees FOR SYSTEM_TIME CONTAINED IN (@start, @end)
WHERE empid = @empid;
Листинг 11. Эквивалент коду листинга 10
<Здесь объявляются переменные и осуществляется назначение>

SELECT *
FROM dbo.Employees
WHERE empid = @empid
  AND sysstart >= @start
  AND sysend <= @end

UNION ALL

SELECT *
FROM dbo.EmployeesHistory
WHERE empid = @empid
  AND sysstart >= @start
  AND sysend <= @end;
Листинг 12. Запрос иерархии на заданный момент времени
DECLARE @datetime AS DATETIME2(0) = '2015-06-01 20:11:01';

SELECT *
FROM dbo.ShowHierarchy() FOR SYSTEM_TIME AS OF @datetime AS F;
Листинг 13. Решения, определяющие функцию с именем ShowHierarchyAt
IF OBJECT_ID(N’dbo.ShowHierarchyAt’, ‘IF’) IS NOT NULL
  DROP FUNCTION dbo.ShowHierarchyAt;
GO
CREATE FUNCTION dbo.ShowHierarchyAt(@datetime AS DATETIME2(0)) RETURNS TABLE
AS
RETURN
  WITH EmpsCTE AS
  (
    SELECT empid, mgrid, empname, 0 AS lvl,
      CAST(‘.’ + CAST(empid AS VARCHAR(10)) + ‘.’ AS VARCHAR(900)) AS path
    FROM dbo.Employees FOR SYSTEM_TIME AS OF @datetime
    WHERE mgrid IS NULL

    UNION ALL

    SELECT S.empid, S.mgrid, S.empname, M.lvl + 1 AS lvl,
      CAST(M.path + CAST(S.empid AS VARCHAR(10)) + ‘.’ AS VARCHAR(900)) AS path
    FROM EmpsCTE AS M
      INNER JOIN dbo.Employees FOR SYSTEM_TIME AS OF @datetime AS S
        ON S.mgrid = M.empid
  )
  SELECT empid, mgrid, empname, lvl, path
  FROM EmpsCTE;
GO
Листинг 14. Запрос с использованием функции ShowHierarchyAt
SELECT
  REPLICATE(' | ', lvl) + '(' + CAST(empid AS VARCHAR(10)) + ') ' + empname
    AS emp
FROM dbo.ShowHierarchyAt('2015-06-01 20:01:41') AS F
ORDER BY path;
Листинг 15. Запрос состояния иерархии на 2015-06-01 20:11:01
SELECT
  REPLICATE(' | ', lvl) + '(' + CAST(empid AS VARCHAR(10)) + ') ' + empname
    AS emp
FROM dbo.ShowHierarchyAt('2015-06-01 20:11:01') AS F
ORDER BY path;
Листинг 16. Запрос на проверку иерархии на 2015-06-01 21:32:20
SELECT
  REPLICATE(' | ', lvl) + '(' + CAST(empid AS VARCHAR(10)) + ') ' + empname
    AS emp
FROM dbo.ShowHierarchyAt('2015-06-01 21:32:20') AS F
ORDER BY path;
Листинг 17. Попытка узнать менеджеров сотрудника в различные моменты времени
DECLARE @PointsInTime AS TABLE
(
  p VARCHAR(10) NOT NULL PRIMARY KEY,
  dt DATETIME2(0) NOT NULL UNIQUE
);

INSERT INTO @PointsInTime(p, dt)
  VALUES('T1', '2015-06-01 19:54:04'),
        ('T2', '2015-06-01 19:54:20'),
        ('T3', '2015-06-01 20:01:41'),
        ('T4', '2015-06-01 20:11:01'),
        ('T5', '2015-06-01 21:32:20');

SELECT P.p, P.dt, E.mgrid
FROM @PointsInTime AS P
  OUTER APPLY ( SELECT *
                FROM dbo.Employees
                  FOR SYSTEM_TIME AS OF P.dt AS E
                WHERE E.empid = 9 ) AS E;
Листинг 18. Обходной прием определения менеджеров для сотрудника
IF OBJECT_ID(N'dbo.EmployeeAt', 'IF') IS NOT NULL
  DROP FUNCTION dbo.EmployeeAt;
GO
CREATE FUNCTION dbo.EmployeeAt(@empid AS INT, @datetime AS DATETIME2(0)) RETURNS TABLE
AS
RETURN
  SELECT empid, mgrid, empname, sysstart, sysend
  FROM dbo.Employees FOR SYSTEM_TIME AS OF @datetime
  WHERE empid = @empid;
GO
Листинг 19. Запрос с использованием встраиваемой функции
DECLARE @PointsInTime AS TABLE
(
  p VARCHAR(10) NOT NULL PRIMARY KEY,
  dt DATETIME2(0) NOT NULL UNIQUE
);

INSERT INTO @PointsInTime(p, dt)
  VALUES('T1', '2015-06-01 19:54:04'),
        ('T2', '2015-06-01 19:54:20'),
        ('T3', '2015-06-01 20:01:41'),
        ('T4', '2015-06-01 20:11:01'),
        ('T5', '2015-06-01 21:32:20');

SELECT P.p, P.dt, E.mgrid
FROM @PointsInTime AS P
  OUTER APPLY dbo.EmployeeAt( 9, P.dt ) AS E;
Листинг 20. Ввод данных в одну строку
<Здесь нужно объявить и ввести табличную переменную @PointsInTime >

WITH C AS
(
  SELECT P.p, E.mgrid
  FROM @PointsInTime AS P
    OUTER APPLY dbo.EmployeeAt( 9, P.dt ) AS E
)
SELECT *
FROM C PIVOT( MAX(mgrid) FOR p IN (T1, T2, T3, T4, T5) ) AS PVT;
Купить номер с этой статьей в PDF