Перед вами первая из серии статей, посвященных возможности длительного хранения как текущих данных, так и данных по состоянию на определенные моменты в прошлом, то есть данных, которые, возможно, позднее были удалены или обновлены. Если для той или иной таблицы активирована системная функция проверки версий, system versioning, система SQL Server сохраняет модифицированные строки как в состоянии на текущий момент времени, так и в виде, который они имели когда-то раньше. Новые операторы запросов дают возможность получать данные, действительные в определенное время или конкретный период.
В версии SQL Server 2016 реализованы средства для работы с новым типом временных таблиц — с временными таблицами с возможностью управления версиями (system-versioned temporal tables), соответствующими стандарту ISO/ANSI SQL:2011. Содержимое строк таблицы с неактивированной функцией управления версиями, system versioning, представлено по состоянию на данный момент, с учетом самых последних изменений. Мы не можем направлять запросы, обращенные к строкам, которые содержались в таблице ранее и сейчас уже удалены или обновлены. В данной статье я оставляю за скобками средства обеспечения многоверсионности содержимого строк в рамках управления конкурентным доступом, такие как реализованная в процессоре обработки транзакций в памяти, In Memory OLTP engine, технология управления конкурентным доступом при наличии версионности, multi-versioning concurrency control (MVCC), а также средства обеспечения многоверсионности строк в моментальных снимках и уровни изоляции подтвержденных моментальных снимков только для чтения для таблиц на основе дисков. В двух статьях этой серии мы рассмотрим возможность длительного хранения (и обращения с запросами) как текущих данных, так и данных по состоянию на определенные моменты в прошлом. Если для той или иной таблицы активирована функция управления версиями, система SQL Server сохраняет модифицированные строки как в состоянии на текущий момент, так и в том виде, который они имели раньше.
Хочу подчеркнуть, что после реализации временных таблиц с управлением версиями система SQL Server обеспечивает сохранение различных версий строк по состоянию на момент времени, когда пользователь модифицировал соответствующие данные (универсальное глобальное время UTC по часам системы, на которой выполняется соответствующий экземпляр SQL Server). Это означает, что пользователь располагает лишь текущей и прошлой версиями строк и не может явно определить период достоверности содержимого строки, скажем, в связи с планируемым изменением. Соответственно, временные таблицы с управлением версиями можно считать подходящим инструментом для ситуаций, когда требуется определение времени модификации, если изменяется состояние строки. В качестве примера можно привести запросы, касающиеся аудита и «путешествий во времени» с целью решения тех или иных юридических проблем.
В стандарт SQL также входят таблицы, содержащие период действия приложений (application-time period tables), в которых пользователи могут явным образом определять период достоверности строки, включая время в будущем. В версии SQL Server 2016 этой функции нет, но надеюсь, что она будет реализована в дальнейшем, ибо в результате пользователи получат множество новых и весьма нужных возможностей. Представим себе, к примеру, таблицу, содержащую цены продуктов. В среде, допускающей использование таблиц с управлением версиями, временем обновления таблицы считается время, когда изменения цен на продукты вступают в силу. Пользователь должен тщательно выбирать время внесения модификаций, чтобы новые цены начинали действовать в тот момент, когда это необходимо. При использовании таблиц, содержащих период действия приложений, периоды достоверности строк определяются явным образом, так что в нашем примере пользователь может определять время в будущем, когда изменения цен на продукты вступят в силу.
Существуют и другие возможности временных таблиц, не раскрытые в первоначальном варианте SQL Server 2016. Но если исходить из того, что «стакан наполовину полон», нельзя не порадоваться тому, что разработчики SQL Server начали реализовывать в своем продукте временные таблицы (см. статью «Планирование работ с SQL Server 2016», опубликованную в Windows IT Pro/RE № 9 за 2016 год). Будем надеяться, что это только начало и что пользователи получат множество новых инструментальных средств для работы в этой сфере, имеющей исключительно важное практическое значение.
В предлагаемой серии статей я намерен дать читателям общее представление о временных таблицах с управлением версиями. В первой статье речь пойдет о создании таблиц и модификации данных; во второй статье я перейду к теме выполнения запросов к данным и изложу свои соображения по вопросам оптимизации. Замечу, что на момент подготовки данной статьи новейшей общедоступной версией пакета SQL Server 2016 является CTP2. Чтобы познакомиться с изменениями и добавлениями в последующих версиях, обратитесь к официальной документации по продукту.
Создание временных таблиц с управлением версиями
Наделить таблицу способностью поддерживать одновременно не только актуальный вариант значений данных, но и последовательность предшествующих или возможных значений, мы можем либо при ее создании как части оператора CREATE TABLE, либо после создания как части оператора ALTER TABLE. Для превращения таблицы в таблицу с управлением версиями необходимы следующие элементы:
- Первичный ключ.
- Параметр SYSTEM_VERSIONING, установленный в значение ON.
-
Два не принимающих значения Null столбца DATETIME2 (
), представляющие начало и конец периода достоверности строки:
— стартовый столбец должен иметь параметр GENERATED ALWAYS AS ROW START;
— конечный столбец должен иметь параметр GENERATED ALWAYS AS ROW END.
-
Указание столбцов периода: PERIOD FOR SYSTEM_TIME (
, ). - Связанная таблица истории изменений (ее можно создать средствами SQL Server) для хранения прежних состояний модифицированных строк.
Далее в статье я буду использовать демонстрационную базу данных TemporalDB, а внутри нее — таблицу с управлением версиями Employees, где будут храниться строки данных истории изменений. Для создания демонстрационной базы данных, если она еще не создана, и для удаления всех ранее использовавшихся экземпляров таблиц Employees и EmployeesHistory, если таковые существуют, воспользуйтесь кодом листинга 1.
При условии соблюдения упомянутых выше требований выполнение кода из листинга 2 приведет к созданию таблицы с управлением версиями Employees. Системе SQL Server будет предписано сформировать сопутствующую таблицу истории изменений с заданным именем EmployeesHistory.
Если соответствующей таблицы истории изменений еще не существует, SQL Server создает новую на базе текущего определения таблицы, но без первичного ключа с активированным кластеризованным индексом (empid, systart, sysend).
На экране 1 показано, как системно-версионная таблица Employees и сопутствующая таблица истории изменений отображаются в среде SQL Server Management Studio (SSMS).
.jpg) |
| Экран 1. Текущая таблица и таблица истории изменений в обозревателе объектов Object Explorer |
Обратите внимание: таблица истории изменений отображается не как отдельный элемент, а непосредственно под сопутствующей текущей таблицей. Отметим также, что SSMS в скобках определяет таблицы как таблицу с управлением версиями и таблицу истории изменений. Если вы хотите программно определить, является ли та или иная таблица временной, можете воспользоваться функцией OBJECTPROPERTY со свойством TableTemporalType. Функция возвращает «2», если таблица с управлением версиями, и «3» — если таблица с историей изменений.
Если же при определении таблицы в качестве таблицы с управлением версиями пользователь не задаст собственное имя для таблицы истории изменений, SQL Server определит для нее имя по образцу MSSQL_TemporalHistoryFor_
Представьте себе, что у вас уже есть таблица Employees с текущими данными и что вы хотите преобразовать ее в таблицу с управлением версиями. Для решения этой задачи нужно будет добавить столбцы для начала и конца периода (с заданными по умолчанию значениями, поскольку эти столбцы не должны принимать значения Null), указание PERIOD, назначить параметру system versioning значение on и ассоциировать данную таблицу с новой или существующей таблицей истории изменений. В листинге 3 показано, как может выглядеть ваш код, предназначенный для достижения этой цели (сейчас вам не нужно запускать его, поскольку ваша таблица Employees уже является таблицей с управлением версиями).
Как уже отмечалось, код добавляет столбцы периода с принимаемыми по умолчанию ограничениями потому, что эти столбцы не должны принимать значения Null. Поскольку принимаемые по умолчанию значения задаются в столбцах периода в новых строках, вы можете не указывать ограничения по умолчанию, ибо в предстоящих модификациях система SQL Server будет автоматически присваивать значения в этих столбцах исходя из времени модификации.
После активации функции system versioning для той или иной таблицы пользователь лишается возможности вносить такие изменения с помощью DDL, которые добавляют, изменяют или удаляют текущую таблицу, а также таблицу истории изменений. Кроме того, он не может модифицировать данные непосредственно в таблице истории изменений. Пользователю разрешается создавать и удалять индексы в таблицах. Если вам нужно будет внести изменение, которое не принимается, когда функция управления версиями активирована, первым делом отключите эту функцию, внесите изменение, а затем активируйте функцию снова. Рекомендуется выполнять весь процесс в рамках одной транзакции, чтобы осуществить блокировку схемы и сохранять ее до внесения изменения.
Код для осуществления таких изменений может выглядеть так, как показано в листинге 4 (не выполняйте его, этот код приводится только для иллюстрации).
В процессе активации функции управления версиями и создания ссылки на существующую таблицу истории изменений SQL Server будет всегда выполнять проверку целостности структуры и выяснять, удовлетворяются ли требования схемы (первичный ключ, обозначение столбцов периода, тип, возможность принимать значение Null). Однако вы можете указать, должна ли система SQL Server наряду с этим выполнять проверку целостности данных (по умолчанию). В ходе этой проверки подтверждается, что значения в столбце окончания периода больше или равны соответствующим значениям столбца начала периода, а также что периоды для различных версий в одной и той же строке (с одним и тем же первичным ключом) не накладываются друг на друга.
Индексирование
SQL Server дает пользователю возможность управлять процессом индексирования текущей таблицы и таблицы истории изменений. Если в процессе оптимизации как хранилища, так и производительности вы предпочитаете работать с технологией хранения данных в одном столбце, columnstore (в этом случае вам понадобится редакция Enterprise), то можете применять кластеризованные индексы columnstore к обеим таблицам. Но имейте в виду, что для работы с текущей таблицей требуется первичный ключ и что первичный ключ нужно применять принудительно с помощью уникального индекса rowstore на базе двоичного дерева. SQL Server допускает применение к двум упомянутым таблицам различных индексов. К примеру, таблица истории изменений с течением времени чаще всего становится значительно объемнее, чем таблица текущая, так что в таблице истории изменений лучше применять технологию columnstore. Если вы полагаете, что в работе с текущей таблицей целесообразно использовать технологию rowstore, так и поступайте, а технологию columnstore будете задействовать только при обработке таблицы истории изменений.
Если вы поручите формирование таблицы истории изменений системе SQL Server, она автоматически создаст кластеризованный индекс rowstore с активированной функцией постраничного сжатия и со списком ключей на базе столбцов: (
По состоянию на сегодня таблица Employees нашей демонстрационной базы данных располагает только некластеризованным индексом для столбца empid, который система SQL Server создала для обеспечения обязательного применения первичного ключа. Для создания кластеризованного индекса таблицы со списком ключей на базе столбца первичного ключа, за которым следуют столбцы начала и окончания системного периода, используйте следующий код:
CREATE UNIQUE CLUSTERED INDEX ix_Employees ON dbo.Employees(empid, sysstart, sysend);
Если бы вы хотели сформировать кластеризованный индекс columnstore для текущей таблицы, вы могли бы сделать это либо в рамках определения таблицы, либо после ее создания следующим образом (не делайте этого сейчас, поскольку в нашем примере мы хотим использовать кластеризованный индекс rowstore):
CREATE CLUSTERED COLUMNSTORE INDEX ix_Employees ON dbo.Employees;
Теперь о таблице истории изменений. Как уже отмечалось, если ее создание вы поручите SQL Server, система сформирует и кластеризованный индекс для этой таблицы. Ознакомиться с результатами индексирования таблицы истории изменений, выполненного системой SQL Server, вы сможете, запустив код из листинга 5.
Данный запрос возвращает результат, представленный на экране 2.
.jpg) |
| Экран 2. Результаты индексирования таблицы истории изменений |
Вы также можете адресовать запрос представлению sys.partitions. Это позволит вам убедиться, что система SQL Server применила постраничное сжатие. Если бы вы хотели на данном этапе изменить стратегию индексирования и перейти с формата хранения данных rowstore к формату хранения columnstore, вы могли бы это сделать, выполнив следующий код (в данном случае не нужно его запускать, поскольку в нашем примере мы хотим использовать применяемый по умолчанию индекс rowstore):
CREATE CLUSTERED COLUMNSTORE INDEX ix_EmployeesHistory ON dbo.EmployeesHistory WITH (DROP_EXISTING = ON);
Во второй статье серии я изложу еще некоторые соображения об индексировании в рамках обсуждения проблемы формирования запросов к данным.
Модификация данных
Одно из преимуществ, получаемых нами при использовании встроенной функции временных таблиц с управлением версиями, состоит в том, что все хлопоты, связанные с сохранением прежних версий в таблице истории изменений, возлагаются на SQL Server. Приложение представляет свои обычные модификации к текущей таблице, а SQL Server создает предыдущие версии и записывает их в таблицу истории изменений. Вы можете создать собственное решение, скажем, с использованием триггеров, но для этого потребуются дополнительные ресурсы разработки. Более того, надо отметить, что существует ряд аспектов встроенной функции, которые не так-то просто имитировать.
Когда пользователь вставляет в таблицу новые строки, SQL Server задает столбцу начала системного периода значение, соответствующее времени вставки транзакции (по универсальному глобальному времени), а столбцу окончания системного периода — значение, соответствующее максимально допустимому значению для этого типа. Поскольку в нашей таблице для столбцов периода мы использовали точный нуль (точность составляет одну секунду), максимальное поддерживаемое значение составляет 9999-12-31 23:59:59. Допустим, что сейчас время в соответствии со стандартом универсального глобального времени 2015-06-01 19:54:04 UTC (назовем его T1). Во время T1 вы запускаете следующий код с целью добавления двух строк:
INSERT INTO dbo.Employees(empid,
mgrid, empname)
VALUES(1, NULL, ‘David’),
(2, 1, ‘Eitan’);
Отметим, что в ходе выполнения явно выраженной транзакции SQL Server записывает время выполнения оператора BEGIN TRAN, а не то время, когда был выполнен первый или текущий оператор внутри данной транзакции. Предположим, к примеру, что вы запускаете оператор BEGIN TRAN в листинге 6 во время T2 (которому в моей системе соответствует показатель 2015-06-01 19:54:20).
Хотя между двумя операторами INSERT внутри данной транзакции имеется задержка продолжительностью в пять секунд, всем строкам, добавленным транзакцией, в качестве времени начала системного периода будет назначено время T2. Кроме того, если между моментом BEGIN TRAN и моментом выполнения первого оператора внутри выполняемой транзакции имела место некая задержка, значение будет иметь время BEGIN TRAN.
Добавьте несколько новых строк в момент времени T3 (которому в моей системе соответствует показатель 2015-06-01 20:01:41). Для этого нужно выполнить код из листинга 7.
В этот момент опросите текущую таблицу:
SELECT * FROM dbo.Employees;
На своей системе я получил результат, показанный на экране 3.
.jpg) |
| Экран 3. Результаты проверки состояния текущей таблицы |
Очевидно, что у вас значения в столбце sysstart будут иными.
Направим запрос к таблице истории изменений:
SELECT * FROM dbo.EmployeesHistory;
Поскольку до сих пор мы только добавляли строки, в распоряжении SQL Server не было более старых версий, которые можно было бы записать в таблицу истории изменений. Так что на данный момент эта таблица пуста (экран 4).
.jpg) |
| Экран 4. Состояние таблицы истории изменений |
Когда пользователь удаляет строку из таблицы, SQL Server переносит эту строку из текущей таблицы в таблицу истории изменений. При этом в качестве времени окончания системного периода указывается время начала модифицирующей транзакции. А когда пользователь обновляет строку в текущей таблице, SQL Server рассматривает данную операцию как удаление с последующей вставкой, а именно:
- Система переносит состояние, в котором строка пребывала до обновления, в таблицу истории изменений, указав в качестве времени окончания системного периода время начала модифицирующей транзакции.
- Система вставляет в текущую таблицу строку, перешедшую в новое состояние — состояние после обновления, указав в качестве времени начала системного периода время начала транзакции, а в качестве времени окончания системного периода — максимальное значение внутри данного типа.
Для демонстрации операций удаления и обновления запустите код листинга 8 в момент времени T4 (которому в моей системе соответствует показатель 2015-06-01 20:11:01).
Затем внесите еще несколько изменений в момент времени T5 (которому в моей системе соответствует показатель 2015-06-0 21:32:20) (листинг 9).
Выполните запрос к текущей таблице:
SELECT * FROM dbo.Employees;
На экране 5 показаны результаты, полученные на моей системе после внесения изменений, произведенного в момент времени T5.
.jpg) |
| Экран 5. Содержимое таблицы Employees после внесения изменений в момент времени T5 |
Выполните запрос к таблице истории изменений:
SELECT * FROM dbo.EmployeesHistory;
На экране 6 представлены результаты, полученные на моей системе после времени T5.
.jpg) |
| Экран 6. Содержимое таблицы EmployeesHistory после внесения изменений в момент времени T5 |
Разумеется, вы получите другие значения; они будут зависеть от моментов времени выполнения транзакций на вашей системе. Вы можете сопоставить моменты начала транзакций с моментами времени от T1 до T5 и найти их в полученных вами результатах.
Итак, в первой статье я рассказал о временных таблицах с управлением версиями — новой функции, реализованной в версии SQL Server 2016. Мы рассмотрели, как активировать эту функцию для той или иной таблицы; я изложил свои соображения по поводу индексирования и пояснил, что происходит в ходе модификации данных пользователем. Я показал, что работающие с новыми средствами пользователи получают ряд преимуществ в отношении экономии ресурсов, расходуемых на разработку и тестирование, — преимуществ, которых не имеют потребители, эксплуатирующие заказные решения. И это еще не все. Во второй статье серии я расскажу об организации запросов к данным и способах оптимизации.
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;
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) GENERATED ALWAYS AS ROW START NOT NULL, sysend DATETIME2(0) GENERATED ALWAYS AS ROW END NOT NULL, PERIOD FOR SYSTEM_TIME (sysstart, sysend) ) WITH ( SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.EmployeesHistory ) );
BEGIN TRAN;
-- Добавьте необходимые столбцы периода и обозначение
ALTER TABLE dbo.Employees ADD
sysstart DATETIME2(0) GENERATED ALWAYS AS ROW START NOT NULL
CONSTRAINT DFT_Employees_sysstart DEFAULT('19000101'),
sysend DATETIME2(0) GENERATED ALWAYS AS ROW END NOT NULL
CONSTRAINT DFT_Employees_sysend DEFAULT('99991231 23:59:59'),
PERIOD FOR SYSTEM_TIME (sysstart, sysend);
-- Удалите временные ограничения DEFAULT
ALTER TABLE dbo.Employees
DROP CONSTRAINT DFT_Employees_sysstart, DFT_Employees_sysend;
-- Активируйте функцию system versioning
ALTER TABLE dbo.Employees
SET ( SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.EmployeesHistory ) );
COMMIT TRAN;
BEGIN TRAN; -- Деактивируйте функцию system versioning ALTER TABLE dbo.Employees SET ( SYSTEM_VERSIONING = OFF ); ... внесите ваше изменение здесь... -- Снова активируйте функцию system versioning ALTER TABLE dbo.Employees SET ( SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.EmployeesHistory, DATA_CONSISTENCY_CHECK = ON ) ); COMMIT TRAN;
SELECT name, type_desc,
STUFF(
( SELECT N',' + C.name AS [text()]
FROM sys.sysindexkeys AS K
INNER JOIN sys.columns AS C
ON K.id = C.object_id
AND K.colid = C.column_id
WHERE K.id = I.object_id
AND K.indid = I.index_id
ORDER BY K.keyno
FOR XML PATH('') ), 1, 1, N'') AS index_keys
FROM sys.indexes AS I
WHERE object_id = OBJECT_ID(N'dbo.EmployeesHistory');
BEGIN TRAN; -- T2 PRINT 'Transaction start time: ' + CONVERT(CHAR(19), SYSDATETIME(), 121); INSERT INTO dbo.Employees(empid, mgrid, empname) VALUES(4, 2, 'Seraph'), (5, 2, 'Jiru'); WAITFOR DELAY '00:00:05'; INSERT INTO dbo.Employees(empid, mgrid, empname) VALUES(6, 2, 'Steve'); PRINT 'Transaction end time: ' + CONVERT(CHAR(19), SYSDATETIME(), 121); COMMIT TRAN;
INSERT INTO dbo.Employees(empid, mgrid, empname) VALUES(8, 5, 'Lilach'), (10, 5, 'Sean'), (3, 1, 'Ina'), (7, 3, 'Aaron'), (9, 7, 'Rita'), (11, 7, 'Gabriel'), (12, 9, 'Emilia'), (13, 9, 'Michael'), (14, 9, 'Didi');
BEGIN TRAN; DELETE FROM dbo.Employees WHERE empid IN (13, 14); UPDATE dbo.Employees SET mgrid = 3 WHERE empid IN(9, 11); COMMIT TRAN;
BEGIN TRAN; UPDATE dbo.Employees SET mgrid = 4 WHERE empid IN(7, 9); UPDATE dbo.Employees SET mgrid = 3 WHERE empid = 6; UPDATE dbo.Employees SET mgrid = 6 WHERE empid = 11; DELETE FROM dbo.Employees WHERE empid = 12; COMMIT TRAN;