Мы продолжаем изучение реализованного в версии SQL Server 2016 нового средства — временных таблиц, в частности кода и демонстрационных данных. Это завершающая статья серии, а первая («Cоздание временных таблиц и модификация данных») была опубликована в предыдущем номере журнала. Таблицы с управлением версиями — это новое средство, реализованное в системе Microsoft SQL Server 2016. В первой части речь шла о том, что представляют собой такие таблицы, как они создаются и как вносятся изменения в данные, которые хранятся в подобных таблицах. В данной статье основное внимание уделяется организации запросов к данным, а также соображениям, касающимся оптимизации.
Напомню, что на момент подготовки статьи новейшей общедоступной версией пакета SQL Server 2016 является версия CTP2. Чтобы познакомиться с изменениями и добавлениями в последующих версиях, обратитесь к официальной документации по продукту.
Если вы хотите выполнять описываемые в настоящей статье запросы и получать те же результаты, что и в моих примерах, вам нужно будет включить в свои таблицы соответствующие демонстрационные данные. С помощью кода, приведенного в листинге 1, создайте текущую таблицу и таблицу истории изменений (Employees и EmployeesHistory) и введите в них демонстрационные данные.
Запустите следующий код, чтобы увидеть содержимое текущей таблицы:
SELECT * FROM dbo.Employees;
На экране 1 показан результат выполнения данного запроса.
.jpg) |
| Экран 1. Содержимое таблицы Employees |
Запустите следующий код для отображения содержимого таблицы истории изменений:
SELECT * FROM dbo.EmployeesHistory;
Результат выполнения данного запроса приведен на экране 2.
.jpg) |
| Экран 2. Содержимое таблицы EmployeesHistory |
Теперь в ваших таблицах содержатся те же демонстрационные данные, что и в моих, и мы можем приступить к подготовке запросов. И здесь я прошу иметь в виду следующее: выполняя примеры по модификации данных, приведенные в первой статье серии, я уложился в короткий промежуток времени (буквально в пару часов). Именно поэтому упомянутые примеры содержат сведения за столь небольшой период. В реальных условиях подобные изменения обычно происходят на протяжении нескольких лет.
Соображения по запросам к данным и оптимизации
Содержащиеся во временной таблице сведения хранятся в двух таблицах (в текущей и истории изменений), но приложение может и не иметь сведений об этом. Приложение должно взаимодействовать только с одной таблицей. Если ваш запрос касается лишь текущих данных, вам следует опрашивать только текущую таблицу и не использовать специальные операторы. Предположим, что вы хотите получить сведения о служащих на текущий момент. Выполните следующий запрос:
SELECT * FROM dbo.Employees;
План этого запроса показан на рисунке 1. Он предполагает просмотр кластеризованного индекса текущей таблицы; разумеется, проверка таблицы истории изменений при этом не предусматривается.
.jpg) |
| Рисунок 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 …FROMFOR 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.
.jpg) |
| Рисунок 2. План запроса с использованием оператора FOR SYSTEM_TIME AS OF @datetime |
Код (или решение) генерирует выходные данные, показанные на экране 3 (посмотрите, правильно ли вы назвали строки, которые должны быть возвращены).
.jpg) |
| Экран 3. Результаты запроса информации о сотрудниках |
Ясно, что при использовании оператора FOR SYSTEM_TIME мы получаем гораздо более простое и естественное решение, чем в случаях, когда эта конструкция не применяется. Данный метод может значительно повысить удобство чтения и сопровождения вашего кода.
Замечу, что следующие соображения применимы лишь в тех случаях, когда используются построчные индексы.
Возможно, у вас возникает вопрос: почему план предусматривает не поиск, а сканирование индексов? Дело в том, что, как вы, вероятно, помните, в качестве ведущего ключа текущих кластеризованных индексов таблиц выступает столбец empid, за которым следуют столбцы systart и sysend, а в нашем запросе фильтрация по столбцу empid не производится. Рассмотрим пример, в котором с помощью фильтра отбираются сведения о конкретном сотруднике (листинг 4). План данного запроса показан на рисунке 3.
.jpg) |
| Рисунок 3. План с процедурами поиска |
На этот раз план включает в себя процедуры поиска в индексах. Если вы предполагаете, что в дальнейшем вам придется часто формировать запросы, не предусматривающие фильтрацию конкретного значения первичного ключа, то вполне вероятно, вы захотите также создать индекс столбцов системных периодов без столбца с ведущим первичным ключом.
Говоря о проблемах индексирования, следует отметить еще одно важное обстоятельство. При работе с предикатами нескольких диапазонов только один из них может использоваться в качестве предиката поиска (см. свойство Seek Predicates в плане); остальные используются в качестве остаточных предикатов (см. свойство Predicate в плане). Это означает, что в целях достижения оптимальной производительности (с тем чтобы сканировать меньшее число страниц в листе индексов) целесообразно формировать список ключей индекса следующим образом: (
Если вам требуется более подробная информация по индексированию, имейте в виду, что я подробно освещаю эту тему в статье «Советы по оптимизации для нескольких предикатов принадлежности диапазону» (части 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 дополнение к стандарту.
Приведенные в таблице предикаты представляют строки, удовлетворяющие требованиям различных подвыражений.
.jpg) |
На рисунке 4 вы видите иллюстрации, графически представляющие строки, которые удовлетворяют требованиям различных подвыражений.
.jpg) |
| Рисунок 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'.
.jpg) |
| Экран 4. Выходные данные для значения @datetime = ‘2015-06-01 20:11:01’ |
А на экране 5 показаны выходные данные для значения @datetime = '2015-06-01 20:11:00'.
.jpg) |
| Экран 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).
.jpg) |
| Экран 6. Результаты запросов листингов 6 и 7 |
Подвыражение FOR SYSTEM_TIME BETWEEN @start AND @end напоминает предыдущее с той лишь разницей, что значение предиката к systart здесь меньше или равно входным данным @end (а не просто «меньше, чем»). Требованиям удовлетворяют строки, соответствующие сочетанию предикатов: sysstart <= @end AND sysend > @start. В листинге 8 приведен пример с этим подвыражением.
Данный запрос эквивалентен коду в листинге 9.
Мы получаем следующие выходные данные, на сей раз включающие три строковые версии для сотрудника (в отличие от двух строковых версий, которые мы получили при использовании предыдущего подвыражения с использованием тех же входных данных), как показано на экране 7.
.jpg) |
| Экран 7. Результаты запросов листингов 8 и 9 |
И наконец, четвертое подвыражение — FOR SYSTEM_TIME CONTAINED IN (@start, @end). В отличие от всех ранее рассмотренных, оно представляет собой расширение стандарта, предложенное специалистами Microsoft. В данном случае удовлетворяющими требованиям считаются такие строки, где значение sysstart совпадает или следует за входным значением @start, а значение sysend совпадает или располагается перед входным значением @end (требованиям удовлетворяют строки, в которых sysstart >= @start AND sysend <= @end). Иными словами, системный период должен содержаться внутри входного периода. В листинге 10 показан пример, иллюстрирующий использование данного подвыражения.
Данный запрос эквивалентен коду в листинге 11.
Этот запрос генерирует следующие выходные данные, демонстрируя две удовлетворяющие требованиям версии строки со сведениями, которые касаются указанного во входных данных сотрудника (экран 8).
.jpg) |
| Экран 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).
.jpg) |
| Экран 9. Состояние иерархии для 2015-06-01 20:01:41 |
Посмотрим иерархию по состоянию на 2015-06-01 20:11:01 (листинг 15). Получим выходные данные, приведенные на экране 10.
.jpg) |
| Экран 10. Состояние иерархии на 2015-06-01 20:11:01 |
Посмотрим иерархию по состоянию на 2015-06-01 21:32:20 (листинг 16). Получим выходные данные, как на экране 11.
.jpg) |
| Экран 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.
.jpg) |
| Экран 12. Выходные данные запроса из листинга 19 |
Что называется, шутки ради приведу код, показывающий, как эти данные вводятся в одну строку (листинг 20). Этот код генерирует выходные данные на экране 13.
.jpg) |
| Экран 13. Выходные данные листинга 20 |
Итак, в версии SQL Server 2016 реализованы средства для работы с временными таблицами с управлением версиями, которые позволяют фиксировать текущие и исторические состояния данных на момент времени, когда эти данные модифицировались. Я надеюсь, что в дальнейшем будут созданы и средства для работы с таблицами с учетом периода действия приложения; таким образом, периоды действительности данных будут определяться приложением, а не временем внесения изменений.
С момента появления новой функции прошло совсем немного времени, и статистических данных, касающихся производительности, собрано пока мало. И хотя Microsoft провозглашает некоторые общие принципы относительно того, как следует проводить индексирование, на сегодня мы не имеем специализированных индексов, разработанных для временных данных. Конечно, нужно будет учитывать, сколько версий вы хотите сохранять для одной строки и как будут выглядеть ваши запросы. Но можно предположить, что для работы с новыми таблицами вполне сгодятся универсальные индексы хранения данных columnstore и rowstore. К примеру, исключение сегмента columnstore, возможно, позволит пользователям довольно эффективно отсеивать данные, не представляющие для них интереса. Как бы то ни было, со временем опыта у нас будет больше.
Другим небольшим улучшением, которое мы увидим в будущем, станет возможность определять столбцы системного периода как полностью скрытые. В системе DB2, например, такая возможность уже поддерживается. Идея состоит в том, что при запуске SELECT* по временной таблице эти столбцы не будут возвращаться, пока на них не будет сделано явной ссылки. Я также могу упомянуть и некоторые другие небольшие возможности, которые пропущены, например связанные с табличными выражениями и взаимосвязями. Не будем жалеть о «полупустом стакане» и выражать недовольство предпринятыми разработчиком усилиями. Меня уже очень радуют первоначальные результаты изменений в SQL Server, относящиеся к такой важной области, как временные таблицы.
-- Создайте базу данных 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 ) );
DECLARE @datetime AS DATETIME2(0) = ‘2015-06-01 20:11:01’; SELECT * FROM dbo.Employees FOR SYSTEM_TIME AS OF @datetime;
<Здесь объявляются переменные и назначения> SELECT * FROM dbo.Employees WHERE sysstart <= @datetime AND sysend > @datetime UNION ALL SELECT * FROM dbo.EmployeesHistory WHERE sysstart <= @datetime AND sysend > @datetime;
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;
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;
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;
<Здесь объявляются переменные и осуществляется назначение> 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;
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;
<Здесь определяются переменные и осуществляется назначение> 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;
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;
<Здесь объявляются переменные и осуществляется назначение> 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;
DECLARE @datetime AS DATETIME2(0) = '2015-06-01 20:11:01'; SELECT * FROM dbo.ShowHierarchy() FOR SYSTEM_TIME AS OF @datetime AS F;
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
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;
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;
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;
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;
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
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;
<Здесь нужно объявить и ввести табличную переменную @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;