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
	,FName VARCHAR(50)
	,PERIOD FOR SYSTEM_TIME (FromDateTime,EndDateTime)

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
CREATE TABLE dbo.Test_TemporalTable
	,FName VARCHAR(50)
	,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
FROM Test_TemporalTable

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'

-- Delete ID=2
DELETE Test_TemporalTable

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

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

FROM Test_TemporalTable

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.

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:

DROP TABLE dbo.Test_TemporalTable

Read in More Detail…


DBA with PowerShell : How to get service status using PowerShell in SQL Server query

Hello friends,

This is second post of “DBA with PowerShell” series. In this series we try to fetch some useful information of SQL Instance environment

Last post “DBA with PowerShell : Get perfmon counters using PowerShell” was about fetching critical information like CPU, Memory, IO utilization and insert it  into SQL Database so we can generate meaningful reports. In this post, we will see how to fetch list of services and its status .It is very helpful in case when we need to monitor any specific service status across multiple servers.

You need to pass the machine name or just simply put “.” for local machine’s services status.

DECLARE @MachineName VARCHAR(128) = '.'  --# Put Machine Name e.g. Mac01 OR put "." for local machine
, @QueryStr VARCHAR(4000)


-- Build Query to get service info
SET @QueryStr = 'PowerShell.exe -c "$a = 1; Get-WmiObject -ComputerName '+@MachineName+'  Win32_Service | Where-Object {$_.Name -like ''*''} | SELECT SystemName, (@{Name=''Service Name''; Exp=''Name''}), (@{Name=''LogOn As''; Exp=''StartName''}), State| ForEach-Object {New-Object psObject -Property @{''SrNo''=$a;''SystemName''= [String]$a + ''^''+ $_.SystemName; ''Service Name'' = [String]$a + ''^''+$_.''Service Name''; ''LogOn As'' = [String]$a + ''^''+ $_.''LogOn As''; ''State'' = [String]$a + ''^''+ $_.State};$a ++}| Sort-Object SrNo |Format-List -Property  SystemName, ''Service Name'', ''LogOn As'', State "'

-- Insert data into a table variable
EXEC xp_cmdshell @QueryStr

-- Deleteing unwanted data

-- Converting into presentable format
SELECT [Tag],[SystemName], [Service Name] ,[LogOn As],[State]
, LTRIM(RTRIM(SUBSTRING(QutPut,CHARINDEX('^',QutPut)+1,len(QutPut)))) Val
CHARINDEX('^',LTRIM(RTRIM(SUBSTRING(QutPut,CHARINDEX(':',QutPut)+1,len(QutPut)))))-1) Tag
FROM @Service
MAX(Val) FOR Head IN ([SystemName], [Service Name] ,[LogOn As],[State])
) Dt


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.
    • 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!!!


List down folders underlying file info using SQL query

Sometimes we need to list down file/folder’s information from a folder like filename, extension, created date and modified date etc.

The below query can give you the list of all content’s info from given folder path.

SQL Query:


IF OBJECT_ID('Tempdb..#FileDetails') IS NOT NULL
DROP TABLE #FileDetails

CREATE TABLE #FileDetails (FileDetails VARCHAR(MAX))

INSERT INTO #FileDetails (FileDetails)
EXEC ('EXEC XP_CMDSHELL ''powershell.exe -c "Get-ChildItem '''''+@FolderPath+''''' | SELECT Name,CreationTime,LastWriteTime,Extension|foreach{$_.Name+''''@''''+$_.CreationTime+''''|''''+$_.LastWriteTime+''''#''''+$_.Extension}"''')

SELECT SUBSTRING(FileDetails,0,CHARINDEX('@',FileDetails)) AS [File Name]
, CONVERT(DATETIME,SUBSTRING(FileDetails,CHARINDEX('@',FileDetails)+1,19)) AS [Creation Date Time]
, CONVERT(DATETIME,SUBSTRING(FileDetails,CHARINDEX('|',FileDetails)+1,19)) AS [Modified Date Time]
, ISNULL(NULLIF(SUBSTRING(FileDetails,CHARINDEX('#',FileDetails)+1,LEN(FileDetails)),''),'Directory') AS [Extension]
FROM #FileDetails

Output Content:

  Column Name Description Remarks
File Name Name of content  File name, Folder name etc.
Creation Date Time File/Folder creation date-time
Modified Date Time Last Modified date-time
Extension File extension  like.. .sql, .xlxs etc.


How to recover Tempdb system database step by step

Hello Friends,

We all know Tempdb is very important component of SQL SERVER Instance and there are some incidents where things goes crazy due to Tempdb.

I have some crazy experiences with Tempdb like giving wrong path OR forget to specify extension while shifting Tempdb database or Somehow Tempdb got crashed so the SQL Instance couldn’t able to work and will give  error like “Path is not found for Tempdb database” or “Specified path is not found”.

It’s bit difficult to recover Tempdb back on track in this situation. But using command prompt we can do that.

You can follow the below steps to recover Tempdb database if SQL Instance is not able to bring up because not able to create Tempdb.

Step 01: STOP ALL Services of sql instance.
Step 02: Open cmd and execute below command:
For Default SQL Instance:
       NET START MSSQLSERVER /f /T3608 -- to bypass system db recovery.
For Named Instance:
       NET START MSSQL$/f/T3608 

Step 03: Open SQLCMD using below command in cmd prompt:

Step 04: Execute below code to change tempdb path in SQLCMD.

ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev' , FILENAME = '.mdf' )
ALTER DATABASE tempdb MODIFY FILE (NAME = 'templog' , FILENAME = '.ldf' )

Step 05: Exit from SQLCMD USING EXIT.

Step 06: Stop SQL Service:


Step 07: Start services from config manager.

Then Enjoy!!!

Hope this post help someone….

Multi value parameter passed to stored procedure is not working in SSRS

Hello Friends,

Today, I was facing very  unusual problem in SSRS-2012 and that was very frustrating.

I had created a SSRS report with multi valued parameters and just for testing; I put the SQL query in text format inside its data-set and used the parameter with IN clause (like ColumnName IN (@ParameterName)) and as we know SSRS has the capability to handle multi value parameter; need not to handle manually like we need to do in Stored Procedures.

After some days, when My development got over I decided to change my text query to stored procedure for better performance and created the stored procedure with required parameters and handled multi value passing and filter data accordingly and was trying to run the report but It was not working!!!

I checked the parameter by displaying the parameter’s value but that was showing me #Error….. It was very frustrating that usual stuff was not working. I searched over the net but  didn’t find anything.

Then I decided to apply very famous  method of developers called “Hit & Try”!!!

Then finally came to know that some how SSRS remembers the nature of the parameter for what it had created. In my case; It had created for IN clause not for passing parameter to a stored procedure ( here I want to highlight that it is not related to .data file; I deleted it and tried that one as well).

Then, I deleted all the parameters from the .rdl file and refresh the data-set. It created all the parameter used in stored procedure. I linked them back with their respective data-sets and run the report and it worked!!!!!

So this time I senses that it is going to pass for a stored procedure and it worked. It is strange but it worked for me.

If anyone facing the same problem then delete all parameters then refresh the databases. I will work and don’t forget to take backup of .rdl file before it to prevent any consequences.

How to know default location of data & log files in SQL Server

Hello Friends,

If I say, How I can create a database dynamically? you most probably tell me “easy man”!!! , just put CREATE DATABASE script in dynamic query string with database name as a parameter and execute it, whenever you want. Absolutely correct!!!;

But If I say I have number of servers and don’t want to go and manually alter the script with it’s appropriate file-path location. Then, above idea will fail; because, we put file-path (log & data files) as a plain text in the query string.

To serve this purpose, SQL Server 2012 onward, very interesting parameters named “InstanceDefaultDataPath” & “InstanceDefaultLogPath” added in SERVERPROPERTY function.

This gives you default location, configured for data and log files respectively.  Try below code ….

SELECT SERVERPROPERTY('InstanceDefaultDataPath') AS [Data File Path]
, SERVERPROPERTY('InstanceDefaultLogPath') AS [Log File Path]