Темпоральные таблицы SQL Server: практические рекомендации

Tags: SQL Server, SQL, таблица

Friday, April 5, 2019 4:46:44 PM

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

Темпоральные таблицы или таблицы с системным управлением версиями были введены как функция базы данных в SQL Server 2016. Это дает нам тип таблицы, которая может предоставлять информацию о данных, которые хранились в любое указанное время, а не только текущие данные. ANSI SQL 2011 сначала определил темпоральную таблицу как функцию базы данных, и теперь она поддерживается в SQL Server.

Наиболее распространенные бизнес-применения для темпоральных таблиц:

  • Медленно меняющиеся измерения. Темпоральные таблицы предоставляют более простой способ запрашивать данные, которые являются текущими в течение определенного периода времени, такие как данные с разделением по времени, что является хорошо известной проблемой в базах данных хранилищ данных.
  • Аудит данных. Темпоральные таблицы предоставляют контрольный журнал для определения того, когда данные были изменены в «родительской» таблице. Это помогает удовлетворить требования нормативного соответствия и проводить экспертизу данных, когда это необходимо, путем отслеживания и аудита изменений данных с течением времени.
  • Исправление или восстановление повреждений на уровне записи. Создание способа «отмены» изменения данных в строке таблицы без простоя в случае случайного удаления или обновления записи. Таким образом, предыдущая версия данных может быть извлечена из таблицы истории и вставлена ​​обратно в «родительскую» таблицу. - Это помогает, когда кто-то (или из-за некоторых ошибок приложения) случайно удаляет данные, и вы хотите вернуться к ним или восстановить их.
  • Воспроизведение финансовых отчетов, счетов и выписок с правильными данными на дату выдачи документа. Темпоральные таблицы позволяют запрашивать данные, как это было в конкретный момент времени, чтобы проверить состояние данных, как это было тогда.
  • Анализ тенденций путем понимания того, как данные изменяются с течением времени в ходе текущей бизнес-деятельности, и для расчета тенденций изменения данных с течением времени.

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

CREATE TABLE dbo.Department

   (

   DeptID INT NOT NULL,

   DeptName VARCHAR(50) NOT NULL,

   ManagerID INT NULL,

   ParentDeptID INT NULL,

   Created DATETIME NOT NULL

     CONSTRAINT DF_Department_Created DEFAULT GETDATE(),

   CONSTRAINT PK_Department_DeptID PRIMARY KEY CLUSTERED(DeptID ASC) ON [PRIMARY]

   ) ON [PRIMARY];

 GO

Следующим шагом является создание таблицы Department_Log с двумя дополнительными столбцами, которые предоставляют историю изменений

  • LogDate
  • LogAction

CREATE TABLE dbo.Department_Log

   (

   DeptID INT NOT NULL,

   DeptName VARCHAR(50) NOT NULL,

   ManagerID INT NULL,

   ParentDeptID INT NULL,

   Created DATETIME NOT NULL,

   LogDate DATETIME NOT NULL,

   LogAction VARCHAR(10) NOT NULL

   ) ON [PRIMARY];

 GO

Когда таблица «истории» будет готова, мы можем создать триггер для регистрации изменений действий UPDATE и DELETE:

CREATE TRIGGER dbo.tr_Department_Log

 ON dbo.Department

 FOR UPDATE, DELETE

 AS

   BEGIN

   SET NOCOUNT ON;

   IF

     (SELECT COUNT(1)

     FROM inserted

       JOIN deleted

         ON Inserted.DeptID = Deleted.DeptID

     ) > 0

     BEGIN

     INSERT dbo.Department_Log

       (DeptID, DeptName, ManagerID, ParentDeptID, Created, LogDate, LogAction)

       SELECT Deleted.DeptID, Deleted.DeptName, Deleted.ManagerID,

         Deleted.ParentDeptID, Deleted.Created, GETDATE(), 'UPDATED'

       FROM deleted;

     END;

   ELSE

     BEGIN

     INSERT dbo.Department_Log

       (DeptID, DeptName, ManagerID, ParentDeptID, Created, LogDate, LogAction)

       SELECT Deleted.DeptID, Deleted.DeptName, Deleted.ManagerID,

         Deleted.ParentDeptID, Deleted.Created, GETDATE(), 'DELETED'

       FROM deleted;

     END;

   SET NOCOUNT OFF;

   END;

 GO

Чтобы продемонстрировать, как таблица Department_Log работает с триггером, мы трижды обновляли строку, в которой DeptID = 1, затем удаляли эту строку и, наконец, при последнем обновлении для столбца DeptName устанавливалось первоначальное значение.

update dbo.Department

SET DeptName = ''

where DeptID = 1

update dbo.Department

SET DeptName = 'Engineering IT'

where DeptID = 1

update dbo.Department

SET DeptName = 'Engineering WEB'

where DeptID = 1

DELETE dbo.Department where DeptID = 1

INSERT dbo.Department(DeptID,  DeptName)

SELECT  DeptID,DeptName

FROM Department_Log WHERE DeptID = 1 and LogAction = 'DELETED'

update dbo.Department

SET DeptName = 'Engineering'

where DeptID = 1

select DeptID,  DeptName,Created,LogDate,LogAction from Department_Log


Результат из таблицы Department_Log показан на следующем рисунке:

 

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

Чтобы перенести таблицу в темпоральную таблицу, для существующей таблицы можно установить параметр темпоральной таблицы. Чтобы создать новую темпоральную таблицу, вам просто нужно установить для параметра темпоральной таблицы значение ON (например, SYSTEM_VERSIONING = ON). Когда опция темпоральной таблицы включена, SQL Server 2016 автоматически генерирует «историческую» таблицу и поддерживает как родительские, так и исторические таблицы, одну для хранения фактических данных, а другую для исторических данных. Столбцы периода темпоральной таблицы SYSTEM_TIME (например, SysStartTime и SysEndTime) позволяют механизму запрашивать данные для другого временного интервала более эффективно. Обновленные или удаленные данные перемещаются в «историческую» таблицу, в то время как «родительская» таблица содержит последнюю версию строки для обновленных записей.

В чем подвох?

Наиболее важные соображения, условия и ограничения темпоральных таблиц:

  • Чтобы связать записи между темпоральной таблицей и таблицей истории, у вас должен быть первичный ключ в темпоральной таблице. Однако таблица истории не может иметь первичный ключ.
  • Тип данных datetime2 должен быть установлен для столбцов периода SYSTEM_TIME (например, SysStartTime и SysEndTime).
  • Когда вы создаете таблицу истории, вы всегда должны указывать и схему, и имя таблицы темпоральной таблицы в таблице истории.
  • Сжатие PAGE является настройкой по умолчанию для таблицы истории.
  • Темпоральные таблицы поддерживают типы данных BLOB-объектов (nvarchar (макс.), Varchar (макс.), Varbinary (макс.), Ntext, текст и изображение), которые могут влиять на стоимость хранения и иметь проблемы с производительностью.
  • Темпоральные и хронологические таблицы должны быть созданы в одной базе данных. Вы не можете использовать связанный сервер для предоставления темпоральных таблиц.
  • Вы не можете использовать ограничения, первичный ключ, внешние ключи или ограничения столбцов для таблиц истории.
  • Вы не можете ссылаться на временные таблицы в индексированных представлениях, в которых есть запросы, использующие предложение FOR SYSTEM_TIME
  • На столбцы периода SYSTEM_TIME нельзя напрямую ссылаться в инструкциях INSERT и UPDATE.
  • Вы не можете использовать TRUNCATE TABLE, когда SYSTEM_VERSIONING включен.
  • Вы не можете напрямую изменять данные в таблице истории.

Создание темпоральной таблицы

Мы показали, как создавать темпоральные и хронологические таблицы в одном сценарии DDL в листинге 1. Как мы упоминали ранее, столбцы SysStartTime и SysEndTime с типом данных datetime2 для обоих столбцов требуются для временной таблицы. Столбец SysStartTime должен быть GENERATED ALWAYS AS ROW START NOT NULL, а SysEndTime должен быть GENERATED ALWAYS AS ROW END NOT NULL. Вы не обязаны указывать значения по умолчанию для этих столбцов, но мы бы порекомендовали это. И столбцы SysStartTime, и SysEndTime должны быть указаны в столбце PERIOD FOR SYSTEM_TIME (как MSDN определил PERIOD, в других публикациях условие вызовов PERIOD).

Примечание. Системные версии столбцов не обязательно должны называться как SysStartTime и SysEndTime, но имена столбцов следует выбирать так, чтобы они отражали функцию захвата времени. Параметры GENERATED ALWAYS AS ROW START/END и PERIOD FOR SYSTEM_TIME (nameFrom, nameTo), включают функцию темпоральной таблицы.

CREATE TABLE Department

   (

   DeptID INT NOT NULL PRIMARY KEY CLUSTERED,

   DeptName VARCHAR(50) NOT NULL,

   ManagerID INT NULL,

   ParentDeptID INT NULL,

   SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START

     CONSTRAINT DF_Department_SysStartTime DEFAULT SYSUTCDATETIME() NOT NULL,

   SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END

     CONSTRAINT DF_Department_SysEndTime

  DEFAULT CONVERT( DATETIME2, '9999-12-31 23:59:59' ) NOT NULL,

   PERIOD FOR SYSTEM_TIME(SysStartTime, SysEndTime)

   )

 WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory));

Листинг 1: Создание темпоральных и исторических таблиц

После создания темпоральной таблицы подчеркнутая таблица истории создается автоматически (рисунок 1), а для истории будет создан CLUSTERED INDEX с обоими столбцами SysStartTime и SysEndTime (или именем, выбранным для определения версии системы). таблица, листинг 2.

CREATE CLUSTERED INDEX ix_DepartmentHistory

 ON dbo.DepartmentHistory

   (SysStartTime ASC,

   SysEndTime ASC

   ) ON [PRIMARY];

Листинг 2: Создание кластерного индекса

Если новый столбец должен быть добавлен в темпоральную таблицу, то необходимо разрешить ALTER TABLE… ADD столбец DDL, и новый столбец будет автоматически отражен в таблице истории.

 

Рисунок 1: Отображение вновь созданных временных и исторических таблиц в Object Explorer.

Однако невозможно использовать DROP TABLE DDL для темпоральной таблицы. Во-первых, SYSTEM_VERSIONING должен быть выключен.

ALTER TABLE Department SET (SYSTEM_VERSIONING = OFF);


Листинг 3: Отключение SYSTEM_VERSIONING для таблицы Department.

Когда для SYSTEM_VERSIONING установлено значение OFF, временные и хронологические таблицы становятся обычными таблицами. Команда DROP TABLE может затем использоваться для этих таблиц.

CREATE TABLE Department_Exist (    

   DeptID int NOT NULL PRIMARY KEY CLUSTERED  

, DeptName varchar(50) NOT NULL  

, ManagerID INT  NULL

, ParentDeptID int NULL )

Листинг 4: Создание таблицы Department_Exist.

ALTER TABLE dbo.Department_Exist

 ADD SysStartTime datetime2 GENERATED ALWAYS AS ROW START  

 CONSTRAINT DF_Department_Exist_SysStartTime DEFAULT SYSUTCDATETIME() NOT NULL,

 SysEndTime datetime2 GENERATED ALWAYS AS ROW END

 CONSTRAINT DF_Department_Exist_SysEndTime DEFAULT CONVERT (DATETIME2, '9999-12-31 23:59:59') NOT NULL,

     PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)

 GO

 

 ALTER TABLE dbo.Department_Exist

     SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Department_ExistHistory))

 GO

Листинг 5: Добавление системных версий столбцов и включение системных версий в таблице Department_Exist.

 

Существующая таблица                                  Преобразованная в темпоральную таблица

Рисунок 2: Сравнение параллельного Department_Exist после преобразования я в темпоральную таблицу

Проверьте метаданные темпоральных таблиц:

-- List temporal tables, temporal_type = 2  

 SELECT tables.object_id, temporal_type, temporal_type_desc, history_table_id,

   tables.name

   FROM sys.tables

   WHERE temporal_type = 2 -- SYSTEM_VERSIONED_TEMPORAL_TABLE

 -- List temporal tables and history tables

 SELECT h.name temporal_name, h.temporal_type_desc, h.temporal_type,

   t.name AS history_table_name, t.temporal_type, t.temporal_type_desc

   FROM sys.tables t

     JOIN sys.tables h

       ON t.object_id = h.history_table_id


Преобразование оптимизированной в памяти таблицы OLTP в таблицу с системным управлением версиями

Хотя процесс преобразования оптимизированной в памяти таблицы OLTP в таблицу с системным управлением версиями аналогичен, есть некоторые различия, которые мы должны рассмотреть и продемонстрировать в этом разделе.

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

  • Оптимизированные в памяти таблицы должны быть долговечными (DURABILITY = SCHEMA_AND_DATA).
  • Оптимизированная таблица истории в памяти создается на основе дисков.
  • Запросы, которые влияют только на родительскую таблицу, могут использоваться в компиляционных модулях T-SQL. Вы не можете использовать временные запросы, используя предложение FOR SYSTEM TIME в изначально скомпилированных модулях, но можно использовать предложение FOR SYSTEM TIME с оптимизированными в памяти таблицами в специальных запросах и не собственных модулях.
  • Внутренняя оптимизированная промежуточная таблица в памяти создается автоматически, чтобы принимать самые последние изменения (INSERT, DELETE) при изменениях в оптимизированной родительской таблице в памяти, когда SYSTEM_VERSIONING = ON.
  • Данные из внутренней промежуточной таблицы, оптимизированной для памяти, регулярно перемещаются в таблицу хронологии на основе диска с помощью задачи асинхронной очистки данных. Этот механизм очистки данных имеет целью сохранить на внутренних буферах памяти менее 10% потребления памяти их родительскими объектами. DMV sys.dm_db_xtp_memory_consumers поможет отследить общее потребление памяти.
  • Сброс данных может быть вызван путем вызова хранимой процедуры sys.sp_xtp_flush_temporal_history @schema_name, @object_name.
  • Когда SYSTEM_VERSIONING = OFF или когда схема таблицы версии системы изменяется путем добавления, удаления или изменения столбцов, все содержимое внутреннего промежуточного буфера перемещается в таблицу истории на основе диска.
  • Запрос исторических данных фактически выполняется на уровне изоляции SNAPSHOT и всегда возвращает объединение между промежуточным буфером в памяти и таблицей на диске без дубликатов.
  • Операции ALTER TABLE, которые изменяют схему таблицы внутри, должны выполнять сброс данных, что может замедлить операцию.

Создание нового оптимизированного в памяти OLTP с включенной опцией System-Versioned Table

DDL для создания новой оптимизированной в памяти таблицы с параметрами темпоральной таблицы очень близок по своему синтаксису к традиционной дисковой таблице. Синтаксис оптимизированной таблицы в памяти имеет блок WITH для первоначальной установки свойств MEMORY_OPTIMIZED и DURABILITY. Поэтому необходимо добавить свойство SYSTEM_VERSIONING через запятую, как показано в листинге 7.

CREATE TABLE dbo.InMemory

 (

  UniqueName varchar(50) NOT NULL

  PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 131072),

  City varchar(32) NULL,

  State_Province varchar(32) NULL,

  LastModified datetime NOT NULL

    , SysStartTime datetime2 GENERATED ALWAYS AS ROW START

  CONSTRAINT DF_InMemory_SysStartTime DEFAULT GETDATE() NOT NULL

, SysEndTime datetime2 GENERATED ALWAYS AS ROW END

  CONSTRAINT DF_InMemory_SysEndTime

  DEFAULT CONVERT (DATETIME2, '9999-12-31 23:59:59') NOT NULL

, PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)

 )

 WITH

 (

     MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA,

     SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.InMemory_History)

 )

Листинг 7: Создание нового оптимизированного в памяти OLTP с включенной опцией System-Versioned Table

Добавление опции System-Versioned Table в существующую оптимизированную в памяти таблицу OLTP.

Как показано в листинге 8, сложнее преобразовать существующую оптимизированную в памяти OLTP-таблицу в таблицу с системным управлением версиями.

Чтобы продемонстрировать этот механизм, давайте создадим таблицу:

CREATE TABLE dbo.InMemoryExist

 (

  UniqueName varchar(50) NOT NULL

  PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 131072),

  City varchar(32) NULL,

  State_Province varchar(32) NULL

 )

 WITH

 (

     MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA

 )

Листинг 8: Создание новой оптимизированной в памяти таблицы OLTP

Когда таблица создана, нам нужно добавить параметры темпоральной таблицы, прежде чем какие-либо данные будут добавлены в таблицу, как показано в листинге 9:

ALTER TABLE dbo.InMemoryExist

 ADD SysStartTime datetime2 GENERATED ALWAYS AS ROW START  

 CONSTRAINT DF_InMemoryExist_SysStartTime DEFAULT SYSUTCDATETIME() NOT NULL,

 SysEndTime datetime2 GENERATED ALWAYS AS ROW END

 CONSTRAINT DF_InMemoryExist_SysEndTime DEFAULT CONVERT (DATETIME2, '9999-12-31 23:59:59') NOT NULL,

     PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)

 GO

 

 ALTER TABLE dbo.InMemoryExist

     SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.InMemoryExist_History))

 GO

Листинг 9: Добавление параметров темпоральной таблицы

Если таблица уже содержит данные, то процесс преобразования таблицы в таблицу с системным управлением версиями является более сложным.

Если InMemoryExist был создан с опцией системного управления версиями, нам нужно удалить таблицы InMemoryExist и InMemoryExist_History:

ALTER TABLE InMemoryExist set (SYSTEM_VERSIONING = OFF)

 GO

 DROP TABLE InMemoryExist

 GO

 DROP TABLE InMemoryExist_History

 GO

Мы воссоздаем таблицу (используйте пример кода, приведенный в листинге 8, приведенном выше в этом разделе). Затем мы вставляем данные в таблицу InMemoryExist:

INSERT InMemoryExist

 select NEWID(),name, type_desc from sys.objects where is_ms_shipped = 0

При запуске кода, чтобы добавить временные параметры таблицы, листинг 9, будут отброшены следующие ошибки:

Msg 13575, Level 16, State 0, Line 21

 ADD PERIOD FOR SYSTEM_TIME failed because table 'database.dbo.InMemoryExist' contains records where end of period is not equal to MAX datetime.

 Msg 13510, Level 16, State 1, Line 29

Невозможно установить для SYSTEM_VERSIONING значение ON, если период SYSTEM_TIME не определен.

Чтобы избежать этих ошибок, нам нужно добавить опции контроля версий системы в более подробных шагах:

--Step 1. Adding nullable the columns

 ALTER TABLE dbo.InMemoryExist ADD SysStartTime datetime2 NULL  

 GO

 ALTER TABLE dbo.InMemoryExist ADD SysEndTime datetime2 NULL  

 GO

 --Step 2 Adding the default constraints

 ALTER TABLE InMemoryExist ADD CONSTRAINT DF_InMemoryExist_SysStartTime DEFAULT GETDATE() FOR SysStartTime;

 GO

 ALTER TABLE InMemoryExist ADD CONSTRAINT DF_InMemoryExist_SysEndTime DEFAULT CAST('9999-12-31 23:59:59.9999999' AS DATETIME2) FOR SysEndTime;

 --Step 3 Updating the column

 UPDATE dbo.InMemoryExist

  SET SysStartTime = '19000101 00:00:00.0000000'

  ,SysEndTime = '99991231 23:59:59.9999999'

 GO

 --Step 4 Setting NOT NULL to the columns

 ALTER TABLE dbo.InMemoryExist ALTER COLUMN SysStartTime datetime2 NOT NULL  

 GO

 ALTER TABLE dbo.InMemoryExist ALTER COLUMN SysEndTime datetime2 NOT NULL  

 GO

 --Step 5 Adding PERIOD FOR SYSTEM_TIME option

 ALTER TABLE InMemoryExist ADD PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)

 GO

 --Step 6 Setting SYSTEM_VERSIONING property

 ALTER TABLE InMemoryExist

     SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.InMemoryExist_History))

 GO

Теперь таблица InMemoryExist включена для процессов контроля версий системы.

Указание свойства DATA_CONSISTENCY_CHECK

Вы должны установить SYSTEM_VERSIONING с DATA_CONSISTENCY_CHECK = ON, чтобы обеспечить проверку целостности данных для существующих данных. Однако свойство DATA_CONSISTENCY_CHECK в настоящее время имеет профиль утечки памяти при использовании. Если вы решили включить DATA_CONSISTENCY_CHECK для временных таблиц, убедитесь, что в вашем экземпляре установлено накопительное обновление 1 для SQL Server 2016.

Вот пример включения свойства DATA_CONSISTENCY_CHECK в существующих таблицах:

ALTER TABLE InMemoryExist

     SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.TableName_History, DATA_CONSISTENCY_CHECK = ON))

 For a new table, DATA_CONSISTENCY_CHECK property enables after HISTORY_TABLE property separated by a comma.

 WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory, DATA_CONSISTENCY_CHECK = ON));

Заключение

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

No Comments

Add a Comment