Creating a System-Versioned Temporal Table – There are three ways to create a system-versioned temporal table with regards to how the history table is specified:
1) Creating a temporal table with an anonymous history table
Creating a temporal table with an anonymous name. This is easiest way to create it when you do not concern about history table name. In the example below, a new table is created with system-versioning enabled and name is automatically decide by SQL Server.
· A 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
· The PERIOD columns are always assumed to be non-nullable, even if nullability is not specified. If the PERIOD 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.
· 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.
· The history table is created as a rowstore table. 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.
· A default clustered index is created for the history table with an auto-generated name in format IX_<history_table_name>. The clustered index contains the PERIOD columns (end, start).
2) 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.
· The schema name is mandatory for the HISTORY_TABLE parameter.
· If the specified schema does not exist, the CREATE TABLE statement will fail.
· 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. If you specify an invalid history table, the CREATE TABLE statement will fail.
Msg 2714, Level 16, State 6, Line 1
There is already an object named ‘DEPARTMENTHISTORY_ALREADYTHERE’ in the database.
Msg 13523, Level 16, State 1, Line 3
Setting SYSTEM_VERSIONING to ON failed because table ‘SQL2016_FeatureTest.dbo.Department’ has 6 columns and table ‘SQL2016_FeatureTest.dbo.DepartmentHistory_AlreadyThere’ has 1 columns.
3) 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. 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.
Reference: Rohit Garg (http://mssqlfun.com/)
You can find and follow MSSQLFUN:-
Other Linked Profiles :-