Archive
Temporal data support in SQL Server 2016 – Part 1
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.
Microsoft SQL Server 2016 Public Preview (CTP2) Available – download now
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
–> 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