What is the system version of temporal TableThe system version of Temporal table is a user table that can save historical modification data and can simply specify time analysis. This temporal table is the system version of Temporal table because the validity of each line is managed by the system. Each temporal table has 2 columns defined by the display, and the type is datetime2. These are used to indicate the validity period. This column is used to mark whether the line is available within the period. In addition to the period column above, the L Temp table also contains references to other tables that the system uses to save row revisions before deleting rows. This additional table can be thought of as the history table, and the main table contains the current row version as the current table. When temporal table is created, you can specify a history table or let the system create a default history table.
How temporary tables workThe system version of the table is a pair of tables, the current table and the history table. These tables contain 2 additional datetime2 fields that define the available age for each row:
- Term start column: The System records the start time of the line on this column, called Sysstarttime
- End of term column: The system records the end time of the line on this column, called Sysendtime
The current table contains the current values for each row. The history table contains only the previous of each row, and Starttime,endtime represents the time period for the row. Here is an example:CREATE TABLE dbo.Employee( [EmployeeID] int not NULL PRIMARY KEY CLUSTERED , [Name] nvarchar( -) not NULL , [Position] varchar( -) not NULL , [Department] varchar( -) not NULL , [Address] nvarchar(1024x768) not NULL , [Annualsalary] decimal (Ten,2) not NULL , [Validfrom] datetime2 (2) GENERATED always as ROW START , [ValidTo] datetime2 (2) GENERATED always as ROW END , PERIOD for System_time (Validfrom, ValidTo) ) with (system_versioning = on (history_table = dbo.employeehistory));You can delete the history_table system in parentheses to automatically create a history table. Insert: For an INSERT, the system sets the Sysstarttime column as the start time of the current transaction, sysendtime the maximum value 9999-12-31update: for update, The system will save the previous row to the history table and set Sysendtime to the start time of the current transaction. The line is closed, and this is the time period for the line to be available. The value of this row on the current table is modified, then Sysstarttime is set to the start time of the current transaction. The sysendtime is set to the maximum time. Delete: For deletion, the system saves the previous row to the history table and sets Sysendtime as the start time of the transaction. The Mark line is closed, and the term record indicates the age of the line. The rows in the current table are deleted. The current query will not be traced to the current row. Only queries with time, or direct query history tables, can be found in this row. Merge: For merge involves 3 operation Insert,update,delete, make different records according to different operation.
Temporary data QueryYou can use the FOR SYSTEM_TIME clause of the SELECT from to query data for the current table and history table. The following is an example of a query:SELECT * from Employee for System_time between ' 2014-01-01 00:00:00.0000000 ' and ' 2015-01-01 00:00:00.0000000 ' WHERE EmployeeID = + ORDER by Validfrom;Note: For System_time will filter out sysstarttime=sysendtime data. These lines operate in the same transaction and produce the same line. You can only return information about system_time filtering by querying the history table
| An expression |
Qualifying Rows |
Description |
| As Of<date_time> |
Sysstarttime <= date_time and SysEndTime > Date_time |
Returns a table whose rows contain the actual (current) value of the past specified point in time. Internally, the staging table and its history tables are federated, and then the results are filtered to return values in rows that are valid at the point in time specified by the <date_time> parameter. If the System_start_time_column_name value is less than or equal to the <date_time> parameter value, and the System_end_time_column_name value is greater than the <date_time> parameter value , the value of this row is considered valid. |
| From<start_date_time>to<end_date_time> |
Sysstarttime < end_date_time and SysEndTime > Start_date_time |
Returns a table that contains the values of all row versions that remain active for the specified time range, whether they start before the <start_date_time> parameter of the from argument or the <end_date_time of the to argument > Stops the activity after the parameter value. Internally, a union is made between the staging table and its history table, and the results are filtered to return the values of all row versions that remain active at any time within the specified time range. Rows that stop active at the lower bound time defined by the from endpoint are excluded, and records that start active at the upper limit of time defined by the to endpoint are also excluded. |
| Between<start_date_time>and<end_date_time> |
Sysstarttime <= end_date_time and SysEndTime > Start_date_time |
Same as the for system_time from <start_date_time>TO<end_date_time> described above, however, the returned row table is included in the <end_date_time> The endpoint defines the upper-bound time-activated line. |
| CONTAINED in (<start_date_time>, <end_date_time>) |
Sysstarttime >= start_date_time and SysEndTime <= end_date_time |
Returns a table that contains the values of all row versions opened and closed within the time range defined by the two datetime values of the CONTAINED in parameter. Records that are activated at the lower bound time, or rows that stop active at the upper limit, are included. |
| All |
All rows |
Returns the union of the rows that belong to the current table and history table. |
Note: You can hide the age column by hidden, deleting a table requires shutting down the system version first ALTER TABLE Employee SET (system_versioning =off )Before you can delete a table
SQL Server 2016 new features: temporal Table
