Let us take the example of tracking the age and height of students who spend a couple of years and are montiored periodically. We can track the montiored parameters using a temporal table.
Creating a temporal table with default history table is convenient option for creating a history table with default configuration.
The Important keyword is System_Versioning. It has two values, ON or OFF. When you set it to ON, the default history table will be created.
The following code creates a temporal table with hsitory table explicitly defined.
---------
USE [Aloha]
GO
CREATE TABLE [dbo].[StudentMonitor]
(
StudentID int Not Null Primary Key Clustered,
LastName varchar(25) Not NULL,
Age float NULL,
Height float 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.StudentMonitor_History))
In the table that is generated the SysStartTime and SysEndTime will be hidden. The period of time is determined by Start Time and end Time.
The highlighted items in the code are required to create the temporal table.
When this statement is run in a query window, the System Versioned table (dbo.StudentMonitor(System-Versioned)) as well as the history table(dbo.StudentMonitor_History)) will be created as shown.
Temporal_Table_OO
Creating a temporal table with default history table is convenient option for creating a history table with default configuration.
The Important keyword is System_Versioning. It has two values, ON or OFF. When you set it to ON, the default history table will be created.
The following code creates a temporal table with hsitory table explicitly defined.
---------
USE [Aloha]
GO
CREATE TABLE [dbo].[StudentMonitor]
(
StudentID int Not Null Primary Key Clustered,
LastName varchar(25) Not NULL,
Age float NULL,
Height float 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.StudentMonitor_History))
In the table that is generated the SysStartTime and SysEndTime will be hidden. The period of time is determined by Start Time and end Time.
The highlighted items in the code are required to create the temporal table.
When this statement is run in a query window, the System Versioned table (dbo.StudentMonitor(System-Versioned)) as well as the history table(dbo.StudentMonitor_History)) will be created as shown.
Temporal_Table_OO
No comments:
Post a Comment