Создание темпоральной таблицы с системным управлением версиями
Применимо к: SQL Server 2016 (13.x) и более поздних версий Azure SQL Database Управляемый экземпляр SQL Azure
Существует три способа создания темпоральной таблицы с системным управлением версиями в зависимости от того, как указывается таблица журнала:
Темпоральная таблица с анонимной таблицей журнала. Укажите схему текущей таблицы, а система сама создаст соответствующую таблицу журнала с автоматически созданным именем.
Темпоральная таблица с таблицей журнала по умолчанию. Укажите имя схемы таблицы журнала и имя таблицы, а система создаст таблицу журнала в этой схеме.
Темпоральная таблица с пользовательской таблицей журнала, созданной заранее. Создайте таблицу журнала, соответствующую вашим потребностям, а затем укажите эту таблицу во время создания темпоральной таблицы.
Создание темпоральной таблицы с анонимной таблицей журнала
Темпоральная таблица с "анонимной" таблицей журнала удобна при быстром создании объектов, особенно в прототипах и тестовых средах. Это также самый простой способ создания темпоральной таблицы, так как для него не требуется параметр в SYSTEM_VERSIONING
предложении . В следующем примере создается новая таблица с включенным управлением версиями системы без определения имени таблицы журнала.
CREATE TABLE Department
(
DeptID INT NOT NULL PRIMARY KEY CLUSTERED,
DeptName VARCHAR(50) NOT NULL,
ManagerID INT NULL,
ParentDeptID INT NULL,
ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON);
Комментарии
Темпоральная таблица с системным управлением версиями должна иметь первичный ключ и иметь ровно один
PERIOD FOR SYSTEM_TIME
определенный с двумя столбцами datetime2 , объявленными какGENERATED ALWAYS AS ROW START
илиGENERATED ALWAYS AS ROW END
.Столбцы
PERIOD
всегда считаются не допускаемыми значения NULL, даже если не указана допустимость значений NULL.PERIOD
Если столбцы явно определены как допускающее значение NULL, инструкция завершается ошибкойCREATE TABLE
.Таблица журнала всегда должна быть выровнена по схеме с текущей или темпоральной таблицей по количеству столбцов, именам столбцов, упорядочению и типам данных.
Анонимная таблица журнала автоматически создается в одной схеме с текущей или темпоральной таблицей.
Имя анонимной таблицы журнала имеет следующий формат: MSSQL_TemporalHistoryFor_<current_temporal_table_object_id>_[суффикс]. Суффикс является необязательным и добавляется только в том случае, если первая часть имени таблицы не является уникальной.
Таблица журнала создается как таблица rowstore. По возможности применяется сжатие PAGE, в противном случае таблица журнала не сжата. Некоторые табличные конфигурации, например разреженные столбцы, не разрешают сжатие.
Для таблицы журнала создается кластеризованный индекс по умолчанию с автоматически созданным именем в формате IX_<history_table_name>. Кластеризованный индекс содержит столбцы
PERIOD
(end, start).Сведения о создании текущей таблицы с оптимизацией для памяти см. в разделе Темпоральные таблицы с системным управлением версиями и таблицы, оптимизированные для памяти.
Создание темпоральной таблицы с таблицей журнала по умолчанию
Темпоральная таблица с таблицей журнала по умолчанию удобна в тех случаях, когда вы хотите контролировать именование, но при этом автоматически создать таблицу журнала с конфигурацией по умолчанию. В следующем примере создается новая таблица с включенным управлением версиями системы с явно определенным именем таблицы журнала.
CREATE TABLE Department
(
DeptID INT NOT NULL PRIMARY KEY CLUSTERED,
DeptName VARCHAR(50) NOT NULL,
ManagerID INT NULL,
ParentDeptID INT NULL,
ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory));
Комментарии
Таблицы журнала создаются по тем же правилам, которые применяются при создании "анонимной" таблицы журнала, с применением следующих особых правил, касающихся именованных таблиц.
- Имя схемы является обязательным для
HISTORY_TABLE
параметра . - Если указанная схема не существует, инструкция завершается ошибкой
CREATE TABLE
. - Если таблица, указанная параметром
HISTORY_TABLE
, уже существует, она проверяется на соответствие только что созданной темпоральной таблице с точки зрения согласованности схемы и темпоральной согласованности данных. Если указана недопустимая таблица журнала, инструкция завершается ошибкойCREATE TABLE
.
Создание темпоральной таблицы с определяемой пользователем таблицей журнала
Создание темпоральной таблицы с пользовательской таблицей журнала удобно, если пользователь хочет указать таблицу журнала с определенными параметрами хранения и различными индексами, настроенными для запросов журнала. В следующем примере создается определяемая пользователем таблица журнала со схемой, выровненной с созданной темпоральной таблицей. Для этой определяемой пользователем таблицы журнала создается кластеризованный индекс columnstore и дополнительный некластеризованный индекс rowstore (дерево B+) для поиска точек. После создания этой пользовательской таблицы журнала создается темпоральная таблица с системным управлением версиями, для которой пользовательская таблица журнала указана как таблица журнала по умолчанию.
CREATE TABLE DepartmentHistory
(
DeptID INT NOT NULL,
DeptName VARCHAR(50) NOT NULL,
ManagerID INT NULL,
ParentDeptID INT NULL,
ValidFrom DATETIME2 NOT NULL,
ValidTo DATETIME2 NOT NULL
);
GO
CREATE CLUSTERED COLUMNSTORE INDEX IX_DepartmentHistory ON DepartmentHistory;
CREATE NONCLUSTERED INDEX IX_DepartmentHistory_ID_Period_Columns ON DepartmentHistory (
ValidTo,
ValidFrom,
DeptID
);
GO
CREATE TABLE Department
(
DeptID int NOT NULL PRIMARY KEY CLUSTERED,
DeptName VARCHAR(50) NOT NULL,
ManagerID INT NULL,
ParentDeptID INT NULL,
ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory));
Комментарии
- Если планируется выполнять аналитические запросы по данным журнала, в которых используются статистические или ранжирующие функции, настоятельно рекомендуется создать кластеризованный индекс columnstore в качестве первичного индекса, так как он позволяет получить очень хорошее сжатие данных и производительность запросов.
- Если основным вариантом использования является аудит данных (т. е. поиск изменений данных журнала для одной строки текущей таблицы), рекомендуется создать таблицу журнала rowstore с кластеризованным индексом.
- Таблица журнала не может иметь первичный ключ, внешние ключи, уникальные индексы, табличные ограничения или триггеры. Ее нельзя настроить для отслеживания измененных данных, отслеживания изменений, репликации транзакций и репликации слиянием.
Преобразование нетемпоральной таблицы в темпоральную таблицу с системным управлением версиями
Вы можете включить системное управление версиями в существующей не темпоральной таблице, например, если вы хотите перенести пользовательское темпоральные решения на встроенную поддержку. Например, имеется набор таблиц, где управление версиями реализуется с помощью триггеров. Темпоральная система управления версиями менее сложна и предоставляет другие преимущества, в том числе:
- неизменяемый журнал;
- Новый синтаксис для запросов с перемещением по времени
- улучшенная производительность DML;
- минимальные затраты на обслуживание.
При преобразовании существующей таблицы рекомендуется использовать HIDDEN
предложение , чтобы скрыть новые PERIOD
столбцы (столбцы ValidFrom
datetime2 и ValidTo
), чтобы избежать влияния на существующие приложения, которые не указывают имена столбцов явным образом (например, SELECT *
или INSERT
без списка столбцов), не предназначены для обработки новых столбцов.
Добавление управления версиями в не темпоральные таблицы
Если вы хотите начать отслеживание изменений для не темпоральной таблицы, содержащей данные, необходимо добавить PERIOD
определение и при необходимости указать имя для пустой таблицы журнала, которую SQL Server создать:
CREATE SCHEMA History;
GO
ALTER TABLE InsurancePolicy
ADD
ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN
CONSTRAINT DF_InsurancePolicy_ValidFrom DEFAULT SYSUTCDATETIME(),
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN
CONSTRAINT DF_InsurancePolicy_ValidTo DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),
PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo);
GO
ALTER TABLE InsurancePolicy
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = History.InsurancePolicy));
GO
Важно!
Точность для DATETIME2
должна совпадать с точностью для базовой таблицы. См. следующие примечания.
Комментарии
- Добавление столбцов, не допускающих значение NULL, с значениями по умолчанию в существующую таблицу с данными — это операция с данными для всех выпусков, отличных от SQL Server Enterprise (в которых это операция с метаданными). При использовании большой существующей таблицы журнала с данными о выпуске SQL Server Standard добавление столбца, отличного от NULL, может оказаться дорогостоящей операцией.
- Необходимо тщательно выбрать ограничения для столбцов начала и окончания периода:
- Значение по умолчанию для столбца начала определяет, начиная с какого момента времени существующие строки должны считаться действительными. Это значение не может быть моментом времени в будущем.
- Время окончания должно быть указано как максимальное значение для заданной точности datetime2, например
9999-12-31 23:59:59
или9999-12-31 23:59:59.9999999
.
- Добавление
PERIOD
выполняет проверка согласованности данных в текущей таблице, чтобы убедиться, что существующие значения столбцов периода являются допустимыми. - Если при включении
SYSTEM_VERSIONING
указана существующая таблица журнала, выполняется проверка согласованности данных как в текущей, так и в таблице журнала. Его можно пропустить, если указатьDATA_CONSISTENCY_CHECK = OFF
в качестве дополнительного параметра.
Перенос существующих таблиц в решение со встроенной поддержкой
В этом примере показано, как перейти с существующего решения на основе триггеров на встроенную темпоральную поддержку. В этом примере предполагается, что текущее пользовательское решение разделяет текущие и исторические данные на две отдельные пользовательские таблицы (ProjectTaskCurrent
и ProjectTaskHistory
).
Если существующее решение использует одну таблицу для хранения фактических и исторических строк, перед выполнением шагов миграции, показанных в следующем примере, необходимо разделить данные на две таблицы. Сначала удалите триггер в будущей темпоральной таблице. Затем убедитесь, что PERIOD
столбцы не могут иметь значение NULL.
/* Drop trigger on future temporal table */
DROP TRIGGER ProjectCurrent_OnUpdateDelete;
/* Make sure that future period columns are non-nullable */
ALTER TABLE ProjectTaskCurrent
ALTER COLUMN [ValidFrom] DATETIME2 NOT NULL;
ALTER TABLE ProjectTaskCurrent
ALTER COLUMN [ValidTo] DATETIME2 NOT NULL;
ALTER TABLE ProjectTaskHistory
ALTER COLUMN [ValidFrom] DATETIME2 NOT NULL;
ALTER TABLE ProjectTaskHistory
ALTER COLUMN [ValidTo] DATETIME2 NOT NULL;
ALTER TABLE ProjectTaskCurrent ADD PERIOD
FOR SYSTEM_TIME([ValidFrom], [ValidTo]);
ALTER TABLE ProjectTaskCurrent SET (
SYSTEM_VERSIONING = ON (
HISTORY_TABLE = dbo.ProjectTaskHistory,
DATA_CONSISTENCY_CHECK = ON
)
);
Комментарии
- Ссылка на существующие столбцы в
PERIOD
определении неявно меняетсяgenerated_always_type
наAS_ROW_START
иAS_ROW_END
для этих столбцов. - Добавление
PERIOD
выполняет проверка согласованности данных в текущей таблице, чтобы убедиться, что существующие значения столбцов периода являются допустимыми. - Настоятельно рекомендуется задать
SYSTEM_VERSIONING
сDATA_CONSISTENCY_CHECK = ON
, чтобы обеспечить проверку согласованности данных для существующих данных. - Если предпочтительнее использовать скрытые столбцы, используйте команду
ALTER TABLE [tableName] ALTER COLUMN [columnName] ADD HIDDEN;
.
Дальнейшие действия
- Темпоральные таблицы
- начало работы с темпоральными таблицами с системным управлением версиями
- Управление хранением исторических данных в темпоральных таблицах с системными версиями
- Темпоральные таблицы с системным управлением версиями и таблицы, оптимизированные для памяти
- Инструкция CREATE TABLE (Transact-SQL)
- Изменение данных в темпоральной таблице с системным управлением версиями
- Запрос данных в темпоральной таблице с системным управлением версиями
- Изменение схемы темпоральной таблицы с системным управлением версиями
- Остановка системного управления версиями в темпоральной таблице с системным управлением версиями
Обратная связь
https://aka.ms/ContentUserFeedback.
Ожидается в ближайшее время: в течение 2024 года мы постепенно откажемся от GitHub Issues как механизма обратной связи для контента и заменим его новой системой обратной связи. Дополнительные сведения см. в разделеОтправить и просмотреть отзыв по