Создание темпоральной таблицы с системным управлением версиямиCreating a system-versioned temporal table

Применимо к:Applies to: даSQL Server 2016 (13.x);SQL Server 2016 (13.x)yesSQL Server 2016 (13.x);SQL Server 2016 (13.x) и более поздние версии ДаБаза данных SQL AzureAzure SQL DatabaseYesБаза данных SQL AzureAzure SQL Database ДаУправляемый экземпляр SQL AzureAzure SQL Managed InstanceYesУправляемый экземпляр SQL AzureAzure SQL Managed InstanceПрименимо к:Applies to: даSQL Server 2016 (13.x);SQL Server 2016 (13.x)yesSQL Server 2016 (13.x);SQL Server 2016 (13.x) and later ДаБаза данных SQL AzureAzure SQL DatabaseYesБаза данных SQL AzureAzure SQL Database ДаУправляемый экземпляр SQL AzureAzure SQL Managed InstanceYesУправляемый экземпляр SQL AzureAzure SQL Managed Instance

Существует три способа создания темпоральной таблицы с системным управлением версиями в зависимости от того, как указывается таблица журнала:There are three ways to create a system-versioned temporal table with regards to how the history table is specified:

  • Темпоральная таблица с анонимной таблицей журнала. Укажите схему текущей таблицы, а система сама создаст соответствующую таблицу журнала с автоматически созданным именем.Temporal table with an anonymous history table: you specify the schema of the current table and let the system create a corresponding history table with auto-generated name.
  • Темпоральная таблица с таблицей журнала по умолчанию. Укажите имя схемы таблицы журнала и имя таблицы, а система создаст таблицу журнала в этой схеме.Temporal table with a default history table: you specify the history table schema name and table name and let the system create a history table in that schema.
  • Темпоральная таблица с пользовательской таблицей журнала, созданной заранее. Создайте таблицу журнала, соответствующую вашим потребностям, а затем укажите эту таблицу во время создания темпоральной таблицы.Temporal table with a user-defined history table created beforehand: you create a history table that fits best your needs and then reference that table during temporal table creation.

Создание темпоральной таблицы с анонимной таблицей журналаCreating a temporal table with an anonymous history table

Темпоральная таблица с "анонимной" таблицей журнала удобна при быстром создании объектов, особенно в прототипах и тестовых средах.Creating a temporal table with an "anonymous" history table is a convenient option for quick object creation, especially in prototypes and test environments. Это самый простой способ создания темпоральной таблицы, так как он не требует указания параметров в предложении SYSTEM_VERSIONING.It is also the simplest way to create a temporal table since it doesn't require any parameter in SYSTEM_VERSIONING clause. В приведенном ниже примере таблица создается с включенным системным управлением версиями без задания имени таблицы журнала.In the example below, a new table is created with system-versioning enabled without defining the name of the history table.

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 NOT NULL
  , SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL
  , PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON);

Важные замечанияImportant remarks

  • Для темпоральной таблицы с системным управлением версиями должен быть определен первичный ключ и ровно один параметр PERIOD FOR SYSTEM_TIME с двумя столбцами datetime2, объявленными как GENERATED ALWAYS AS ROW START / ENDA system-versioned temporal table must have a primary key defined and have exactly one PERIOD FOR SYSTEM_TIME defined with two datetime2 columns, declared as GENERATED ALWAYS AS ROW START / END
  • Столбцы PERIOD всегда считаются не поддерживающими значение NULL, даже если допустимость значения NULL не указана.The PERIOD columns are always assumed to be non-nullable, even if nullability is not specified. Если столбцы PERIOD определены явно как допускающие значение NULL, инструкция CREATE TABLE завершится с ошибкой.If thePERIOD columns are explicitly defined as nullable, the CREATE TABLE statement will fail.
  • Таблица журнала должна быть всегда согласована по схеме с текущей или темпоральной таблицей. Это касается числа столбцов, имен столбцов, порядка и типов данных.The history table must always be schema-aligned with the current or temporal table, in terms of number of columns, column names, ordering and data types.
  • Анонимная таблица журнала автоматически создается в одной схеме с текущей или темпоральной таблицей.An anonymous history table is automatically created in the same schema as current or temporal table.
  • Имя анонимной таблицы журнала имеет следующий формат: MSSQL_TemporalHistoryFor_<ид_объекта_текущей_темпоральной_таблицы>_[суффикс] .The anonymous history table name has the following format: MSSQL_TemporalHistoryFor_<current_temporal_table_object_id>_[suffix]. Суффикс является необязательным и добавляется только в том случае, если первая часть имени таблицы не является уникальной.Suffix is optional and it will be added only if the first part of the table name is not unique.
  • Таблица журнала создается как таблица rowstore.The history table is created as a rowstore table. Если возможно, применяется сжатие PAGE. В противном случае таблица журнала остается без сжатия.PAGE compression is applied if possible, otherwise the history table will be uncompressed. Некоторые табличные конфигурации, например "Разреженные столбцы", не разрешают сжатие.For example, some table configurations, such as SPARSE columns, do not allow compression.
  • Для таблицы журнала с автоматически создаваемым именем в формате IX_<history_table_name> создается кластеризованный индекс по умолчанию.A default clustered index is created for the history table with an auto-generated name in format IX_<history_table_name>. Кластеризованный индекс содержит столбцы PERIOD (конец, начало).The clustered index contains the PERIOD columns (end, start).
  • Сведения о создании текущей таблицы с оптимизацией для памяти см. в разделе Темпоральные таблицы с системным управлением версиями и таблицы, оптимизированные для памяти.To create the current table as a memory-optimized table, see System-Versioned Temporal Tables with Memory-Optimized Tables.

Создание темпоральной таблицы с таблицей журнала по умолчаниюCreating a temporal table with a default history table

Темпоральная таблица с таблицей журнала по умолчанию удобна в тех случаях, когда вы хотите контролировать именование, но при этом автоматически создать таблицу журнала с конфигурацией по умолчанию.Creating a temporal table with a default history table is a convenient option when you want to control naming and still rely on the system to create the history table with the default configuration. В приведенном ниже примере таблица создается с включенным системным управлением версиями и с явно заданным именем таблицы журнала.In the example below, a new table is created with system-versioning enabled with the name of the history table explicitly defined.

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 NOT NULL
  , SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL
  , PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory));

Важные замечанияImportant remarks

Таблицы журнала создаются по тем же правилам, которые применяются при создании "анонимной" таблицы журнала, с применением следующих особых правил, касающихся именованных таблиц.The history table is created using the same rules as apply to creating an "anonymous" history table, with the following rules that apply specifically to the named history table.

  • Для параметра HISTORY_TABLE обязательно использовать имя схемы.The schema name is mandatory for the HISTORY_TABLE parameter.
  • Если указанная схема не существует, инструкция CREATE TABLE завершится с ошибкой.If the specified schema does not exist, the CREATE TABLE statement will fail.
  • Если таблица, заданная параметром HISTORY_TABLE , уже существует, она будет проверена на соответствие с вновь созданной темпоральной таблицей с точки зрения согласованности схемы и согласованности темпоральных данных.If the table specified by the HISTORY_TABLE parameter already exists, it will be validated against the newly created temporal table in terms of schema consistency and temporal data consistency. Если будет указана недопустимая таблица журнала, инструкция CREATE TABLE завершится с ошибкой.If you specify an invalid history table, the CREATE TABLE statement will fail.

Создание темпоральной таблицы с пользовательской таблицей журналаCreating a temporal table with a user-defined history table

Темпоральная таблица с пользовательской таблицей журнала удобна в тех случаях, когда требуется определить таблицу журнала с особыми параметрами хранения и дополнительными индексами.Creating a temporal table with user-defined history table is a convenient option when the user wants to specify a history table with specific storage options and additional indexes. В следующем примере создается пользовательская таблица журнала со схемой, которая согласована с создаваемой темпоральной таблицей.In the example below, a user-defined history table is created with a schema that is aligned with the temporal table that will be created. В этой пользовательской таблице журнала создаются кластеризованный индекс columnstore и дополнительный некластеризованный индекс rowstore (сбалансированное дерево) для уточняющих запросов.To this user-defined history table, a clustered columnstore index and additional non clustered rowstore (B-tree) index is created for point lookups. После создания этой пользовательской таблицы журнала создается темпоральная таблица с системным управлением версиями, для которой пользовательская таблица журнала указана как таблица журнала по умолчанию.After this user-defined history table is created, the system-versioned temporal table is created specifying the user-defined history table as the default history table.

CREATE TABLE DepartmentHistory
(
    DeptID INT NOT NULL
  , DeptName VARCHAR(50) NOT NULL
  , ManagerID INT NULL
  , ParentDeptID INT NULL
  , SysStartTime DATETIME2 NOT NULL
  , SysEndTime DATETIME2 NOT NULL
);
GO

CREATE CLUSTERED COLUMNSTORE INDEX IX_DepartmentHistory
    ON DepartmentHistory;
CREATE NONCLUSTERED INDEX IX_DepartmentHistory_ID_PERIOD_COLUMNS
    ON DepartmentHistory (SysEndTime, SysStartTime, DeptID);
GO

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 NOT NULL
  , SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL
  , PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory));

Важные замечанияImportant remarks

  • Если планируется выполнять аналитические запросы по данным журнала, в которых используются статистические или ранжирующие функции, настоятельно рекомендуется создать кластеризованный индекс columnstore в качестве первичного индекса, так как он позволяет получить очень хорошее сжатие данных и производительность запросов.If you plan to run analytic queries on the historical data that employs aggregates or windowing functions, creating a clustered columnstore as a primary index is highly recommended for compression and query performance.
  • Если основной задачей является аудит данных (т. е. поиск изменений данных журнала для одной строки текущей таблицы), рекомендуется создать таблицу журнала rowstore с кластеризованным индексом.If the primary use case is data audit (i.e. searching for historical changes for a single row from the current table), then a good choice is to create rowstore history table with a clustered index
  • Таблица журнала не может иметь первичный ключ, внешние ключи, уникальные индексы, ограничения таблицы или триггеры.The history table cannot have a primary key, foreign keys, unique indexes, table constraints or triggers. Она не может быть настроена для отслеживания измененных данных, отслеживания изменений, репликации транзакций и репликации слиянием.It cannot be configured for change data capture, change tracking, transactional or merge replication.

Преобразование нетемпоральной таблицы в темпоральную таблицу с системным управлением версиямиAlter non-temporal table to be a system-versioned temporal table

Такое преобразование может потребоваться при необходимости включить системное управление версиями с использованием существующей таблицы, например, если нужно перенести пользовательское темпоральное решение в решение со встроенной поддержкой.When you need to enable system-versioning using an existing table, such as when you wish to migrate a custom temporal solution to built-in support. Например, имеется набор таблиц, где управление версиями реализуется с помощью триггеров.For example, you may have a set of tables where versioning is implemented with triggers. Использование темпоральной системы управления версиями проще и дает дополнительные преимущества, а именно:Using temporal system-versioning is less complex and provides additional benefits including:

  • неизменяемый журнал;Immutable history
  • новый синтаксис запросов, перемещающихся во времени;New syntax for time-travelling queries
  • улучшенная производительность DML;Better DML performance
  • минимальные затраты на обслуживание.Minimal maintenance costs

При преобразовании существующей таблицы рекомендуется использовать предложение HIDDEN для скрытия новых столбцов PERIOD (столбцы datetime2 SysStartTime и SysEndTime), чтобы избежать влияния на существующие приложения, которые явно не указывают имена столбцов (например, SELECT * или INSERT без списка столбцов) и не предназначены для работы с новыми столбцами.When converting an existing table, consider using the HIDDEN clause to hide the new PERIOD columns (the datetime2 columns SysStartTime and SysEndTime) to avoid impacting existing applications that do not explicitly specify column names (e.g. SELECT * or INSERT without column list) are not designed to handle new columns.

Добавление функции управления версиями в нетемпоральные таблицыAdding versioning to non-temporal tables

Если вы хотите начать отслеживать изменения для нетемпоральной таблицы, которая содержит данные, нужно добавить определение PERIOD и при необходимости указать имя для пустой таблицы журнала, которая будет создана SQL Server:If you want to start tracking changes for a non-temporal table that contains the data, you need to add the PERIOD definition and optionally provide a name for the empty history table that SQL Server will create for you:

CREATE SCHEMA History;
GO

ALTER TABLE InsurancePolicy
    ADD
        SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN
            CONSTRAINT DF_SysStart DEFAULT SYSUTCDATETIME()
      , SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN
            CONSTRAINT DF_SysEnd DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),
        PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);
GO

ALTER TABLE InsurancePolicy
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = History.InsurancePolicy));

Важно!

Точность для DATETIME2 должна быть согласована с точностью для базовой таблицы — см. следующие примечания.The precision for DATETIME2 must align with the precision for the underlying table - see following remarks.

Важные замечанияImportant remarks

  • Добавление в существующую таблицу с данными не допускающих значения NULL столбцов со значениями по умолчанию является операцией, связанной с размером данных, во всех выпусках кроме SQL Server Enterprise Edition (в этом выпуске это операция с метаданными).Adding non-nullable columns with defaults to an existing table with data is a size of data operation on all editions other than SQL Server Enterprise Edition (on which it is a metadata operation). При наличии большой существующей таблицы журнала с данными в SQL Server Standard Edition добавление столбца со значениями, отличными от NULL, может потребовать значительного времени.With a large existing history table with data on SQL Server Standard Edition, adding a non-null column can be an expensive operation.
  • Необходимо тщательно выбрать ограничения для столбцов начала и окончания периода:Constraints for period start and period end columns must be carefully chosen:
    • Значение по умолчанию для столбца начала определяет, начиная с какого момента времени существующие строки должны считаться действительными.Default for start column specifies from which point in time you consider existing rows to be valid. Это значение не может быть моментом времени в будущем.It cannot be specified as a datetime point in the future.
    • Время окончания должно быть указано как максимальное значение для заданной точности datetime2, например 9999-12-31 23:59:59 или 9999-12-31 23:59:59.9999999.End time must be specified as the maximum value for a given datetime2 precision, for example 9999-12-31 23:59:59 or 9999-12-31 23:59:59.9999999.
  • При добавлении периода будет выполнена проверка согласованности данных в текущей таблице, чтобы убедиться в том, что значения по умолчанию для столбцов периода являются допустимыми.Adding period will perform a data consistency check on the current table to make sure that the defaults for period columns are valid.
  • Если при включении SYSTEM_VERSIONINGбудет указана существующая таблица журнала, проверка согласованности данных будет выполнена и в текущей таблице, и в таблице журнала.When an existing history table is specified when enabling SYSTEM_VERSIONING, a data consistency check will be performed across both the current and the history table. Этот шаг можно пропустить, если указать DATA_CONISTENCY_CHECK = OFF в качестве дополнительного параметра.It can be skipped if you specify DATA_CONSISTENCY_CHECK = OFF as an additional parameter.

Перенос существующих таблиц в решение со встроенной поддержкойMigrate existing tables to built-in support

В этом примере показано, как выполнить перенос существующего решения на основе триггеров в решение со встроенной темпоральной поддержкой.This example shows how to migrate an existing solution based on triggers to built-in temporal support. В этом примере предполагается, что в имеющемся пользовательском решении текущие данные и данные журнала разделены на две отдельные пользовательские таблицы (ProjectTaskCurrent и ProjectTaskHistory).For this example, we assume that the current custom solution splits the current and historical data in two separate user tables (ProjectTaskCurrent and ProjectTaskHistory). Если в имеющемся решении используется одна таблица для хранения текущих строк и строк журнала, следует разбить данные на две таблицы до переноса, описанного в этом примере:If your existing solution uses single table to store actual and historical rows, then you should split the data into two tables prior to the migration steps shown in this example:

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

Важные замечанияImportant remarks

  • Ссылка на существующие столбцы в определении PERIOD неявно заменяет параметр generated_always_type на AS_ROW_START и AS_ROW_END для этих столбцов.Referencing existing columns in PERIOD definition implicitly changes generated_always_type to AS_ROW_START and AS_ROW_END for those columns.
  • При добавлении PERIOD будет выполнена проверка согласованности данных в текущей таблице, чтобы убедиться в том, что существующие значения для столбцов периода являются допустимыми.Adding PERIOD will perform a data consistency check on current table to make sure that the existing values for period columns are valid
  • Настоятельно рекомендуется присвоить параметру SYSTEM_VERSIONING значение DATA_CONSISTENCY_CHECK = ON , чтобы принудительно выполнять проверки соответствия существующих данных.It is highly recommended to set SYSTEM_VERSIONING with DATA_CONSISTENCY_CHECK = ON to enforce data consistency checks on existing data.
  • Если предпочтительнее использовать скрытые столбцы, используйте команду ALTER TABLE [tableName] ALTER COLUMN [columnName] ADD HIDDEN;.If hidden columns are preferred, use the command ALTER TABLE [tableName] ALTER COLUMN [columnName] ADD HIDDEN;.

Дальнейшие действияNext steps