Temporal table in SQL Server 2016

Hello Friends,

System-versioned temporal table OR temporal table is a new type of user table with data history. It maintains historical data like SCD-2 (Slowly Changing Dimension type2) .

This concept has introduced in ANSI SQL 2011 and incorporated by MS SQL Server in SQL Server 2016 onward. It’s a very great feature for auditing purpose.

Facts of Temporal Table:

Below mentioned features are tested and verified.

  1. It has two parts system-versioned temporal table and History table.
  2. Temporal table maintains only data level history.
  3. PRIMARY KEY is compulsory while defining system versioning.
  4. History table is read only.
  5. We can create Indexes/statistics in history table for better query performance.
  6. We cannot add column, alter datatype, add trigger, add constraint, add check constraint etc. in history table. More precisely, anything which requires schema changes or data restrictions are not allowed.
  7. It requires 2 additional columns with datetime2 datatype to store time-stamps (UTC date-time). Also, it doesn’t affect any DML operation as it is hidden for such statements.
  8. If user defined history table name already exists then it will become history table with old data.
  9. If we add column in existing temporal table then it will maintain history for it without any additional setting.
  10. We can drop column from temporal table even if it has SYSTEM_VERSIONING = ON and it loses the history for that column as well.

How to create Temporal Table:

Using below CREATE TABLE statement, we can create temporal table.

CREATE TABLE dbo.Test_TemporalTable
(
	 ID INT IDENTITY PRIMARY KEY
	,FName VARCHAR(50) 
	,FromDateTime DATETIME2 GENERATED ALWAYS AS ROW START
	,EndDateTime DATETIME2 GENERATED ALWAYS AS ROW END
	,PERIOD FOR SYSTEM_TIME (FromDateTime,EndDateTime)
)
 WITH (SYSTEM_VERSIONING = ON)

FromDateTime: Data history start date time.

EndDateTime : Data history end datetime.

SYSTEM_VERSIONG = ON: signifies that system versioning has enabled.

You can see on Object Explorer; “system-versioned” tag with temporal table. Which defines it as temporal table.

Where is the history table?

You can find the history table just beneath the temporal table in object explore with “History” tag. Also it has similar structure as temporal table but without primary key. Above CREATE TABLE statement has no user -defined name for history table so it will be create with prefix MSSQL_ and suffix object_id. In case, if the auto generated history table already exists then SQL Server will add a random number after it.

See below snap:

Insert/Update/Delete on system-versioned temporal table:

We can perform Insert/Update/Delete as like normal table. The additional columns are hidden for such operations.

In below SQL, we will create a temporal table with user defined History table and Insert/update/delete data on it.

-- Creating system-versioned table
GO
CREATE TABLE dbo.Test_TemporalTable
(
	 ID INT IDENTITY PRIMARY KEY
	,FName VARCHAR(50) 
	,FromDateTime DATETIME2 GENERATED ALWAYS AS ROW START
	,EndDateTime DATETIME2 GENERATED ALWAYS AS ROW END
	,PERIOD FOR SYSTEM_TIME (FromDateTime,EndDateTime)
)
 
-- Enable system versioning
ALTER TABLE Test_TemporalTable SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Test_TemporalTable_History))  

-- Inserting test data into temporal table

INSERT INTO Test_TemporalTable (FName) VALUES ('Data_01'),('Data_02')

-- Currently no data into History table 
SELECT *
FROM Test_TemporalTable

SELECT *
FROM [dbo].[Test_TemporalTable_History]

After inserting data into temporal table; we can find data only in temporal table whereas History table has no data in it because it has no change till now.

Now, let’s perform some DML operation in temporal table

-- DML operation 
-- Update ID=1
UPDATE Test_TemporalTable
SET FName = 'Data_01_Updated'
WHERE ID = 1

-- Delete ID=2
DELETE Test_TemporalTable
WHERE ID = 2

-- Insert New data
INSERT INTO Test_TemporalTable (FName) VALUES ('Data_03')

-- Now History table has historical data whereas temporal table has current data.

SELECT *
FROM Test_TemporalTable

SELECT *
FROM [dbo].[Test_TemporalTable_History]

Now, we can see the data in history table. The time-stamps are signifying the data validation period.

How to query from temporal table:

We can see history data along with current data using below query.

SELECT *
FROM dbo.Test_TemporalTable
FOR SYSTEM_TIME BETWEEN '2017-01-01 00:00:00' AND '9999-12-31 23:59:59.9999999'
ORDER BY ID,  FromDateTime

Here, a new clause “FOR  SYSTEM_TIME” has introduced in SQL 2016 to filter history time-stamp. You can apply normal WHERE clause after it.

How to drop temporal table:

To drop temporal table we need to OFF system versioning first then it will allow to drop the temporal table. See below code:

ALTER TABLE Test_TemporalTable SET (SYSTEM_VERSIONING = OFF)
GO
DROP TABLE dbo.Test_TemporalTable

Read in More Detail…

#history-table, #sql-server-2016, #temporal-table

R-Services in SQL SERVER 2016 (Part-01)

Hello Friends, SQL Server always comes with exciting features in every new version upgrades. Finally; I have been fortunate enough to start working in SQL SERVER 2016.

In this blog, We will see about integration of “R” language in SQL SERVER.I have gone through multiple MSDN chapters, white papers and learning videos about R-Services, R-language, statistical mathematics including regression analysis and found that its really awesome to have “R” with SQL SERVER.

As we all know, now a day’s data analytics is very exciting stuff to work on and “R” is a very powerful tool to do statistical analysis. The integration of “R” with SQL SERVER is a very powerful combo in terms of data processing and statistical analysis on heavy data.

I would like to break this topic in following points:

  1. Introduction of R language.
  2. What is Statistical analysis?
  3. How to call “R” scripts in SQL Server 2016.
    • Launchpad Service.
    • SP_EXECUTE_EXTERNAL_SCRIPT
    • Different flavors of “R” in SQL Server 2016

R language : “R” is an open source, statistical programming software, which is used for statistical analysis. It can be download from software repository CRAN. “R” is widely used by data scientists for data analysis and also have very wide range of user group. “R” version used in SQL SERVER 2016 is R-Open and called R-Services in SQL SERVER.

Statistical Analysis: Statistical analysis is a mathematical way for data sampling & data analysis. Using it we try to build relationship between dependent and independent variables and find a valid pattern so we can predict the future consequences.

Dependent variable: For which we are concerned about OR we wants to predict.
Independent variable: On which dependent variable depends.

for e.g.  Consider a simple data-set of sales and purchase as follows

Revenue   | Production_Cost | Advertisement_Cost
$10,000   | $5000                    | $3000
$20,000   | $1000                    | $4000

Here; “Revenue” depends upon “Production_Cost” & “Advertisement_Cost”. So “Revenue” is dependent variable and “Production_Cost” & “Advertisement_Cost” are independent variable. In other words we can say that we can control the independent variable but we can’t control dependent variable; that’s why we wants to predict it…Right!!!

Launchpad Service: Its a new service introduced in SQL Server 2016 to launch external scripts. As of now its “R” and most probably “python” in SQL 2017. You can see a new service in SQL Configuration Manager called “SQL Server Launchpad (<Instance Name>)

It has introduced to reduce main SQL engine work load while executing external scripts from SSMS. Some external commands can be executed using xp_cmdshell like powershell scripts and shares the resource of SQL SERVER. So Microsoft has introduced new Launchpad Services to execute such external scripts.

SP_EXECUTE_EXTERNAL_SCRIPT: Sp_Execute_External_Script system stored procedure helps to execute external scripts (currently, R-Services scripts) from SSMS. It can be enabled using “sp_configure ‘external scripts enabled’, 1;”. A sample code is as follows:


 EXEC   sp_execute_external_script @language = N'R'  
				 , @script = N'<R Script>'  
				 , @input_data_1 = N'<SELECT Query from database>'  
				 , @output_data_1_name = N'output dataset name'  
				   WITH RESULT SETS ((<OUTPUT dataset''s column name and its datatype>)); 

 

Flavors of “R-Services” in SQL SERVER 2016: Its has 2 server components and 1 client component as follows

  1. R Services (In-Database) : Specially designed for SQL SERVER data-sources.
  2. Microsoft R Server (Standalone) : It supports multiple data-sources along with SQL SERVER.
  3. Microsoft R Client: Its client environment to build code.

Note: You can install “R Services (In-Database)” and “Microsoft R Server (Standalone)”  in same instance and it will work as well but Microsoft recommends, not to install both features in a single instance.

I have done some practical with R-Services (In-database)  and its really amazing; will share in my future posts.

Hope! you like this introduction of R-Services in SQL 2016.

 

Thank you!!!

 

#r, #r-in-sql-server-2016, #r-services, #rservices, #sql-server-2016, #sql2016