SQL with Manoj

This coming release of new SQL Server 2016 will also support for Temporal Data as a new feature called Temporal Tables or System-Versioned Tables.
 

A Temporal Data is tightly related to a Time period and used to handle the time-varying data. It provides the ability to look at data trends, types of data changes, and the overall data evolution within your database.

A Temporal Table is a table for which a PERIOD definition exists and which contains system columns with a datatype of datetime2 into which the period of validity is recorded by the system. The Temporal table has an associated History Table into which the system records all prior versions of each record with their period of validity.

With a Temporal Table, the value of each record at any point in time can be determined, rather than just the current value of each record.
 

The Temporal Data is closely related to Slowly Changing Dimensions (SCD) Type-6 method, but the whole mechanism is internally and automatically managed by the SQL Server DB Engine.
 

–> With Temporal Data support you can:

1. Time Travel back & forth and understand business trends over time.

2. Track and Audit all data changes.

3. Maintain a Slowly Changing Dimension (SCD) for decision support applications

4. Recover from accidental data changes by repairing record-level corruptions.

–> To create a Temporal Table following clauses are used with CREATE TABLE statement:

1. Two additional Start & End date Audit columns of datetime2 datatype for capturing the validity of records. You can use any meaningful column name here, we will use ValidFrom and ValidTo column names in our example below.

2. Both the column names have to be specified in and as PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo) clause with the column list.

3. Specify WITH (SYSTEM_VERSIONING = ON) option at the end of the CREATE TABLE statement with optional (HISTORY_TABLE = {History_Table_Name}) option.
 

The above CREATE TABLE statement will create 2 tables:

1. one the parent Temporal or System-Versioned Table

2. and second the History Table
 

–> Now, as I mentioned in Step #3 above, you have an option to specify the History Table name or not. So let’s check both the options here:
 

–> Option #1: Create Temporal Table [dbo].[Department] with automatically named History table:

USE [TestManDB]
GO

CREATE TABLE dbo.Department 
(
    DepartmentID		int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, 
    DepartmentName		varchar(50) NOT NULL, 
    ManagerID			int NULL, 

    ValidFrom			datetime2 GENERATED ALWAYS AS ROW START NOT NULL, 
    ValidTo				datetime2 GENERATED ALWAYS AS ROW END   NOT NULL,   

    PERIOD FOR SYSTEM_TIME (
		ValidFrom, 
		ValidTo
	)   
)
WITH ( SYSTEM_VERSIONING = ON ); -- No History table name given here
GO

– Check the History Table nested under the Temporal Table dbo.Department . The auto-naming convention goes like this MSSQL_TemporalHistoryFor_{parent_temporal_table_object_id}:

– Let’s Check the metadata of both the tables:

SELECT object_id, temporal_type, temporal_type_desc, history_table_id, name -- Department
FROM SYS.TABLES 
WHERE object_id = OBJECT_ID('dbo.Department', 'U')

SELECT object_id, temporal_type, temporal_type_desc, history_table_id, name -- MSSQL_TemporalHistoryFor_1397580017
FROM SYS.TABLES 
WHERE object_id = ( 
	SELECT history_table_id 
	FROM SYS.TABLES 
	WHERE object_id = OBJECT_ID('dbo.Department', 'U')
)
GO

– Output:

– To DROP both the Tables, first you need to switch OFF the System Versioning on the parent Temporal Table:

ALTER TABLE [dbo].[Department] SET ( SYSTEM_VERSIONING = OFF )
GO
DROP TABLE [dbo].[Department]
GO
DROP TABLE [dbo].[MSSQL_TemporalHistoryFor_1525580473]
GO

–> Option #2: Create Temporal Table [dbo].[Department] with a named History table [dbo].[DepartmentHistory]:

CREATE TABLE dbo.Department 
(
    DepartmentID		int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, 
    DepartmentName		varchar(50) NOT NULL, 
    ManagerID			int NULL, 

    ValidFrom			datetime2 GENERATED ALWAYS AS ROW START NOT NULL, 
    ValidTo				datetime2 GENERATED ALWAYS AS ROW END   NOT NULL,   

    PERIOD FOR SYSTEM_TIME (
		ValidFrom, 
		ValidTo
	)   
)
WITH ( SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory) );
GO

– Check the History Table dbo.DepartmentHistory nested under the parent Temporal Table dbo.Department:

– Let’s Check the metadata of both the tables:

SELECT object_id, temporal_type, temporal_type_desc, history_table_id, name 
FROM SYS.TABLES 
WHERE object_id = OBJECT_ID('dbo.Department', 'U')

SELECT object_id, temporal_type, temporal_type_desc, history_table_id, name  
FROM SYS.TABLES 
WHERE object_id = OBJECT_ID('dbo.DepartmentHistory', 'U')
GO

– Output:

– Final Cleanup, As mentioned above to DROP both the Tables, first you need to switch OFF the System Versioning on the parent Temporal Table by ALTER statement:

ALTER TABLE [dbo].[Department] SET ( SYSTEM_VERSIONING = OFF )
GO
DROP TABLE [dbo].[Department]
GO
DROP TABLE [dbo].[DepartmentHistory]
GO

– Otherwise you will get following error message:

Msg 13552, Level 16, State 1, Line 82
Drop table operation failed on table ‘TestManDB.dbo.Department’ because it is not supported operation on system-versioned temporal tables.
 

–> Limitation of Temporal Tables:

1. Temporal querying over Linked Server is not supported.

2. History table cannot have constraints (PK, FK, Table or Column constraints).

3. INSERT and UPDATE statements cannot reference the SYSTEM_TIME period columns.

4. TRUNCATE TABLE is not supported while SYSTEM_VERSIONING is ON

5. Direct modification of the data in a history table is not permitted.

6. INSTEAD OF triggers are not permitted on either the tables.

7. Usage of Replication technologies is limited.
 

–> You can also check this demo in this video:

In my [next post] we will see a DEMO of Temporal data in SQL Server and how you can Time-Travel with your data and get details about point in time history without any extra effort.