Tuesday, November 28, 2017

Creating a default System Versioned Temporal Table

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

No comments:

Post a Comment

What is SQLOISIM? What is it used for in SQL Server?

SQLIOSIM is a tool for simulating SQL Server IO. SQLIOSIM performs reliability and integrity tests on the disk systems that SQL Server uti...