Note: This topic applies to Microsoft SQL SERVER 2016.
In my Previous Blog post Temporal Table in SQL SERVER 2016 we have learnt about Temporal Table which Microsoft has introduced in SQL SERVER 2016. In this Blog post we will be learning how to implement Temporal Table on a new table?
Step1: First let’s create Demo Database Called SQL2016 Demo.
Output: Observer Image1, Demo Database is created.
Image1:
Step2: In step we will implement Temporal Table or System Versioned table called Department without specifying History_Table option. In below transact SQL we haven’t use HISTORY_TABLE = [History_Table_Name] option, we have just use WITH (SYSTEM_VERSIONING = ON) option. In step 3 I will explain you how to use HISTORY_TABLE option.
Output: With the help of above transact SQL we have implemented a temporal table or System-Versioned table called Department and as we have not specified historical table name so system has created a table called dbo.MSSQL_TemporalHistoryFor_885578193 (History) highlighted in yellow in below Image2. Also History table is underneath the current table. In ideal world it is not easy to remember or workout which table is the history table for Department or any Temporal Table if the table name is like MSSQL_TemporalHistoryFor_%, so in next step we will be implementing Temporal Table with proper history table name?
The Department table has also period definition column called SysStartTime and SysEndTime of datetime2 data type. Please observe in blue circle in below image Image2.
Image2:
Step3: In step we will implement Temporal Table or System Versioned table called Department1 with specifying History_Table option. In below transact SQL we use HISTORY_TABLE = [Department1_History] option. Here in this step I will creating table called Department1 instead of Drop the existing table called Department. To drop table system versioned table, it requires to disable the system versioning on the table then we can drop the table. I will explain how to disable the System version on the table and Drop table or change the schema later on.
Output: So, in this step we implemented a Temporal Table or System-Versioned table called Department and we have specified historical table name called Department1_History so the system has created table called dbo.Department1_Histsory (History) highlighted in yellow in below Image3. Also History table is underneath the current table.
Image3:
So, in this Blog post we learnt how to implement Temporal Table or System versioned table on a new table, with specifying History_Table option and without specifying History_Table option.
Related Post
SQL Skills 