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

Относится к: SQL Server 2016 (13.x) и более поздние версии База данных SQL AzureУправляемый экземпляр SQL AzureSQL Database в Microsoft Fabric

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

  • Временная таблица с анонимной таблицей истории: вы указываете схему текущей таблицы и позволяете системе создать соответствующую таблицу истории с автосгенерированным именем.

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

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

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

Создание временной таблицы с анонимнойтаблицей журнала является удобным способом быстрого создания объектов данных, особенно в прототипах и тестовых окружениях. Это также самый простой способ создания временной таблицы, потому что он не требует никаких параметров в предложении 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
);

Remarks

Системная темпоральная таблица должна иметь определённый первичный ключ и точно одну PERIOD FOR SYSTEM_TIME, связанную с двумя столбцами datetime2, объявленными как GENERATED ALWAYS AS ROW START или GENERATED ALWAYS AS ROW END.

Столбцы PERIOD всегда считаются не допускающими значение NULL, даже если это свойство специально не указано. PERIOD Если столбцы явно определены как допускающие значение NULL, инструкция завершается ошибкойCREATE TABLE.

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

ядро СУБД автоматически создаёт анонимную таблицу истории в той же схеме, что и текущая или временная таблица.

Имя анонимной таблицы истории имеет следующий формат: MSSQL_TemporalHistoryFor_<current_temporal_table_object_id>_<suffix> Суффикс является необязательным и добавляется только в том случае, если первая часть имени таблицы не является уникальной.

Таблица истории создается как таблица rowstore. PAGE сжатие применяется, если это возможно, в противном случае таблица истории остаётся нераспакованной. Например, некоторые конфигурации таблиц, такие как SPARSE столбцы, не разрешают сжатие.

Для таблицы истории создаётся кластерный индекс по умолчанию с автосгенерированным именем в формате IX_<history_table_name>. Кластеризованный индекс содержит PERIOD столбцы (конец, начало).

В базе данных SQL Fabric созданная таблица журнала не отображается в Fabric OneLake.

Сведения о создании текущей таблицы в качестве оптимизированной для памяти таблицы см. в статье "Системные версии темпоральных таблиц с оптимизированными для памяти таблицами".

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

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

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
    )
);

Remarks

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

  • Имя схемы является обязательным для HISTORY_TABLE параметра.

  • Если указанная схема не существует, инструкция CREATE TABLE вызовет ошибку.

  • Если таблица, указанная параметром HISTORY_TABLE , уже существует, она проверяет соответствие созданной временной таблице с точки зрения согласованности схем и согласованности временных данных. Если указать недопустимую таблицу журнала, инструкция завершается ошибкой CREATE TABLE .

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

Создание временной таблицы с пользовательской таблицей истории — удобный вариант, когда вы хотите указать таблицу истории с конкретными параметрами хранения и разными индексами, настроенными на исторические запросы. В следующем примере вы создаёте пользовательскую таблицу истории со схемой, выровненной с временной таблицей. В этой таблице истории есть кластерный индекс столбцевого хранилища и дополнительный некластеризованный индекс строкового хранилища (B-tree) для поиска точек. После создания таблицы истории вы создаёте временную таблицу и указываете пользовательскую таблицу истории как таблицу истории по умолчанию.

Note

В документации термин B-tree обычно используется в ссылке на индексы. В индексах rowstore ядро СУБД реализует дерево B+. Это не относится к индексам columnstore или индексам в таблицах, оптимизированных для памяти. Дополнительные сведения см. в руководстве по архитектуре и проектированию индексов SQL Sql Server и Azure.

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
    )
);

Remarks

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

Если вы планируете использовать темпоральные таблицы для аудита данных (то есть поиск исторических изменений для одной строки из текущей таблицы), следует создать таблицу журнала rowstore с кластеризованным индексом.

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

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

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

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

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

  • Неизменяемая история
  • Новый синтаксис для временных запросов
  • улучшенная производительность DML;
  • минимальные затраты на обслуживание.

При преобразовании существующей таблицы рассмотрите возможность использования предложения HIDDEN, чтобы скрыть новые столбцы PERIOD (столбцы datetime2ValidFrom и 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

Important

Точность DATETIME2 должна соответствовать точности базовой таблицы.

Remarks

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

Remarks

Ссылка на существующие столбцы в определении 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;

Связанные материалы

  • Last updated on