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

Мақала
07/05/2024

Область применения: SQL Server 2016 (13.x) и более поздних версий База данных SQL Azure Управляемый экземпляр 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 столбцы (конец, начало).
Сведения о создании текущей таблицы с оптимизацией для памяти см. в разделе Темпоральные таблицы с системным управлением версиями и таблицы, оптимизированные для памяти.

Создание темпоральной таблицы с таблицей журнала по умолчанию

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

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+ tree) для подстановок точек. После создания этой пользовательской таблицы журнала создается темпоральная таблица с системным управлением версиями, для которой пользовательская таблица журнала указана как таблица журнала по умолчанию.

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 базовой таблицы. См. следующие замечания.

Замечания

Добавление ненуляемых столбцов с значениями по умолчанию в существующую таблицу с данными — это размер операции с данными во всех выпусках, отличных от выпуска 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;.

Бөлісу құралы:

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

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

Замечания

Создание темпоральной таблицы с таблицей журнала по умолчанию

Замечания

Создание темпоральной таблицы с определяемой пользователем таблицей журнала

Замечания

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

Добавление управления версиями в непоральные таблицы

Замечания

Перенос существующих таблиц в решение со встроенной поддержкой

Замечания

Следующие шаги

Кері байланыс

Кері байланыс

Қосымша ресурстар