Temporal Table in SQL SERVER 2016. Removing System Versioning.

Note: This topic applies to Microsoft SQL SERVER 2016.

So far we have covered new feature introduced by Microsoft in SQL SERVER 2016 called Temporal Table, which will be useful for system version of the table. Then I have covered How to implement a temporal table on a new table or creating a new Temporal Table. You can find more information if we have to Migrate existing solution to use system versioning or Temporal Table or System Versioned table.

In this Blog Post, we will be learning on How to remove System Versioning? In our previous example on Changing the schema of a Temporal Table.

The following example shows how to completely remove System Versioning from dbo.SalesPerson Table and drop the dbo.SalesPersonHistory Table. We could also want to drop the PERIOD columns.

You can read the Previous Blog post Changing the schema of a Temporal Table
to continue with this series of the Blog Post or you can start from Temporal Table in SQL SERVER 2016.

Note: we cannot drop either the dbo.SalesPerson or the dbo.SalesPersonHistory tables while system versioning is enabled.

Step1: Below code Remove System Versioning from dbo.SalesPerson table, Drop the Period Column From dbo.SalesPerson table and Drop the dbo.SalesPersonHistory table.

USE SQL2016Demo;
GO
ALTER TABLE dbo.SalesPerson
SET (SYSTEM_VERSIONING = OFF);
ALTER TABLE dbo.SalesPerson
DROP PERIOD FOR SYSTEM_TIME;
DROP TABLE dbo.SalesPersonHistory;

Temporal Table in SQL SERVER 2016. Changing the schema of a Temporal Table.

Note: This topic applies to Microsoft SQL SERVER 2016.

So far we have covered new feature introduced by Microsoft in SQL SERVER 2016 called Temporal Table, which will be useful for system version of the table. Then I have covered How to implement temporal table on a new table or Creating a new Temporal Table. You can find more information if we have to Migrate existing solution to use system versioning or Temporal Table or System Versioned table.

In this Blog Post, we will be learning on How to change the schema of a Temporal Table in the scenario where we need to change the schema of Temporal Table? In our previous example on Migrate existing solution to use system versioning or Temporal Table or System Versioned table, we were maintaining Sales Person’s Region in dbo.SalesPerson and dbo.SalesPersonHistory table. Now business want to track each Sales Person’s Area Sales Manager, which requires a schema changes on a Temporal Table to add a new column called AreaSalesManager, so let’s learn how we can alter the structure of a Temporal table.

Schema changes are blocked while SYSTEM_VERSIONING is ON to prevent the two table becoming out of sync, so in order to change the schema of a temporal table we must first temporarily disable the SYSTEM_VERSIONING by setting it as SYSTEM_VERSIONING = OFF. While SYSTEM_VERSIONING is OFF we can make changes to the both tables independently and then re-enable the SYSTEM_VERSIONING. Perform the above steps within a transaction to maintain data consistency.

The following example demonstrated this technique. You can read the Previous Blog post Migrate existing solution to use system versioning or Temporal Table or System Versioned table
to continue with this series of the Blog Post.

Step1: Below code implements schema changes in Transaction.

BEGIN TRAN
/* Takes schema lock on both tables */
ALTER TABLE dbo.SalesPerson SET (SYSTEM_VERSIONING = OFF)
/* add column to current table */
ALTER TABLE dbo.SalesPerson
ADD AreaSalesManager Varchar (50) NULL;
/* add column to history table */

ALTER TABLE dbo.SalesPersonHistory
ADD AreaSalesManager Varchar (50) NULL;
/* Re-establish versioning again given that this operation is under strict control (other transactions are blocked), DBA chooses to ignore data consistency check in order to make it fast*/

ALTER TABLE dbo.SalesPerson SET (SYSTEM_VERSIONING =ON (HISTORY_TABLE=dbo.SalesPersonHistory, DATA_CONSISTENCY_CHECK = OFF));
COMMIT;
GO

Output: Below Image shows that we have successfully added new column to the dbo.SalesPerson and dbo.SalesPersonHistory tables.

Temporal Table in SQL SERVER 2016.

Note: This topic applies to Microsoft SQL SERVER 2016.

Microsoft have introduces a support for temporal table (Track historic changes) as a database feature in SQL SERVER 2016 which provides built in support for provide information about data stored in the table at any point in time rather than only the data that is current at the moment of time. Let’s understand what is temporal table? , Why do we need temporal table, how does this temporal table work?

What is Temporal Table?

Temporal table is table for which PERIOD definition exists and it contains system columns called as SysStartTime and SysEndTime with a datatype of datetime2 into which the period of validity recorded by the SQL SERVER, and temporal table has associated history table. This historical table holds all the prior versions of each record with their period of validity and this all has been managed by SQL SERVER internally. With temporal table we can determine value of the record at any point in time rather than just the current value of the each record. A temporal table is called as system-versioned table.

Why do we need temporal table?

Real data sources are dynamic and historical data analysis is often key to business success. We can use temporal table in below cases.

Time Trend: To understand the business trends over time or Tracking data changes over time.
Data Audit: Auditing all changes to data.
Slow Changing dimension, useful for data warehouse load for Type 2 Dimension.
Repair Record Level corruption: in the event of accidental update/delete you can easily recovered each record from history table.

How does temporal table work?

Implementations of a temporal table involves pair of tables, current table and history table. With each of these tables, two additional datetime (datetime2 datatype) columns are used to defined period of validity for each record – a system start time (SysStartTime) column and a system end time (SysEndTime) column. The current table contains the current value for each record. The historical table contains the each previous value for each record, if any, and the start time and end time for the period for which it was valid.

INSERTS: On an INSERT of a record the system set the value for the SysStartTime column to the UTC time of the current transaction based on the system clock and set the value for the SysEndTime column to the maximum value of 9999-12-31 – this marks the record as open.

UPDATES: On an UPDATE, the system stored the previous value of the record in the history table and sets the value for the SysEndTime column to the UTC time of the current transaction based on the system clock. This marks the record as closed, with a period recorded for which the record was valid. In the current table record is updated with its new value and system sets the value for the SysStartTime column to the UTC time of the current transaction based on the system clock. The value for the updated record in the current table for the SysEndTime column remains the maximum value of 9999-12-31.

DELETES: On a DELETE, the system stored the previous value of the current record in the history table and sets the value for the SysEndTime column to the UTC time of the current transaction based on the system clock. This marks the record as closed with a period recorded for which the previous record was valid. In the current table the record is removed and queries of the current table will not return this value. Only queries that deal with history data return data for which a record is closed.

MERGE: On a MERGE behaves as an INSERT, an UPDATE, or a DELETE based on the condition for each record.

For More information about Temporal Table please follow:

1) https://msdn.microsoft.com/en-us/library/dn935015%28v=sql.130%29.aspx

2) http://channel9.msdn.com/Shows/Data-Exposed/Temporal-in-SQL-Server-2016

Migrate an Existing Solution to Use System Versioing.