Advertisements

Archive

Posts Tagged ‘Temporal Database’

Temporal data support in SQL Server 2016 – Part 1

June 15, 2015 10 comments

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.
SQL Server 2016 Temporal 01

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.

SQL Server 2016 Temporal 12
 

–> 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}:

SQL Server 2016 Temporal 04

– 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:

SQL Server 2016 Temporal 05

– 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:

SQL Server 2016 Temporal 06

– 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:

SQL Server 2016 Temporal 07

– 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.


Advertisements

Microsoft SQL Server 2016 Public Preview (CTP2) Available – download now

May 28, 2015 3 comments

Just got an email from Microsoft:

Microsoft SQL Server 2016 Public Preview Available – Try it Today!
 

[Register and Download CTP-2 Evaluation version (180 days)]

 

Direct download link:
SQLServer2016CTP2-x64-ENU.box
SQLServer2016CTP2-x64-ENU.exe
 

SQLServer2016CTP2
 

–> Smooth Installation with new setup-option and new features in SSMS:

Microsoft SQL Server 2016, the next major release of Microsoft’s flagship database and analytics platform, provides breakthrough performance for mission critical applications and deeper insights on your data across on-premises and cloud. The first public preview, SQL Server 2016 Community Technology Preview (CTP) 2, is now available for you to download to trial via Microsoft Azure.
 

–> Try it today for an early look at these new capabilities:

– Always Encrypted: helps protect data at rest and in motion

– Stretch Database: dynamically stretch your warm and cold transactional data to Microsoft Azure, demo video.

– Real-time Operational Analytics: our in-memory technologies are enhanced to provide real-time analytics on top of breakthrough transactional performance
 

–> Additional capabilities include:

1. PolyBase: More easily manage relational and non-relational data with the simplicity of T-SQL.

2. AlwaysOn Enhancements: Achieve even higher availability and performance of your secondaries, with up to 3 synchronous replicas, DTC support and round-robin load balancing of the secondaries.

3. Row Level Security: Enables customers to control access to data based on the characteristics of the user. Security is implemented inside the database, requiring no modifications to the application, demo video.

4. Dynamic Data Masking: Supports real-time obfuscation of data so data requesters do not get access to unauthorized data. Helps protect sensitive data even when it is not encrypted, demo video.

5. Native JSON support: Allows easy parsing and storing of JSON and exporting relational data to JSON, demo video.

6. Temporal Database support: Tracks historical data changes with temporal database support.

7. Query Data Store: Acts as a flight data recorder for a database, giving full history of query execution so DBAs can pinpoint expensive/regressed queries and tune query performance.

8. MDS enhancements: Offer enhanced server management capabilities for Master Data Services.

9. Enhanced hybrid backup to Azure: Enables faster backups to Microsoft Azure and faster restores to SQL Server in Azure Virtual Machines. Also, you can stage backups on-premises prior to uploading to Azure.
 

–> Other Benefits:

1. Enhanced in-memory performance provide up to 30x faster transactions, more than 100x faster queries than disk based relational databases and real-time operational analytics

2. New Always Encrypted technology helps protect your data at rest and in motion, on-premises and in the cloud, with master keys sitting with the application, without application changes

3. Built-in advanced analytics provide the scalability and performance benefits of building and running your advanced analytics algorithms directly in the core SQL Server transactional database

4. Business insights through rich visualizations on mobile devices with native apps for Windows, iOS and Android

5. Simplify management of relational and non-relational data with ability to query both through standard T-SQL using PolyBase technology

6. Stretch Database technology keeps more of your customer’s historical data at your fingertips by transparently stretching your warm and cold OLTP data to Microsoft Azure in a secure manner without application changes, demo video.

7. Faster hybrid backups, high availability and disaster recovery scenarios to backup and restore your on-premises databases to Microsoft Azure and place your SQL Server AlwaysOn secondaries in Azure
 

Learn more about SQL Server 2016: SQL Server 2016 Preview Page