System versioned Tables in SQL Server 2016

In SQL Server 2016, System versioned tables are the tables whose data is maintained in the history table. This history is maintained by SQL Server itself. All you need is to specify 2 additional datetime2 columns (SysStartTime and SysEndTime in the example below) and a clause with these two columns as : PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)

To create a system versioned table;

------ System versioned table

CREATE TABLE Department  

(   

     DeptID int NOT NULL PRIMARY KEY CLUSTERED 

   , DeptName varchar(50) NOT NULL 

   , ManagerID INT  NULL 

   , ParentDeptID int NULL 

   , SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL 

   , SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL 

   , PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)    

)   

WITH (SYSTEM_VERSIONING = ON)  

;

The system versioned table creates a temporal table which maintains the history of the data. By default, the name is as MSSQL_TemporalHistoryFor_1541580530. But you can specify the name of this table in the CREATE TABLE statement above;

------ System versioned table with name the temporal table

CREATE TABLE Department  

(   

     DeptID int NOT NULL PRIMARY KEY CLUSTERED 

   , DeptName varchar(50) NOT NULL 

   , ManagerID INT  NULL 

   , ParentDeptID int NULL 

   , SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL 

   , SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL 

   , PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)    

)   

WITH (SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.DepartmentHistory ) )  

;

In above example, dbo.DepartmentHistory is created as temporal table.

In the management studio, the system versioned tables are shown as below;

The above diagram shows the default name of the temporal table. If you have specified the name of your temporal table (dbo.DepartmentHistory in our example), then it will be shown;

You can also rename the temporal table. In sys.objects, these tables are listed as a user_table. To check if any table have a temporal table or not you can query the “OBJECTPROPERTY” for property “TableTemporalType”;

Select OBJECTPROPERTY(OBJECT_ID(N'dbo.Department', N'U'), N'TableTemporalType')

INSERT / UPDATE / DELETE

When we insert any data in the main table (dbo.Department), no data is inserted in the history / temporal table. In case of UPDATE you will see the latest data in main table and the history data in the temporal. The SysStartTime and SysEndTime are updated as per the time of UPDATE statement;

Update [dbo].[Department]

Set DeptName = 'A'

where DeptID = 3

Select * from [dbo].[Department]

Select * from [dbo].[DepartmentHistory]

You can see above that DeptName for DeptID=3 is changed to ‘A’ and the old value ‘D3’ is available in the temporal table. Please note that the copy of the complete record is maintained in the temporal table.

You cannot delete the data directly from Temporal table. When you delete data from main table, the data is stored in temporal table and deleted from the main table.

Delete from [dbo].[Department] where DeptID = 3

Select * from [dbo].[Department]

As you can see the data is deleted from main table. But the record is available in temporal table;

Select * from [dbo].[DepartmentHistory]

When you try to delete from the DepartmentHistory temporal table, it will generate error;

Delete from [dbo].[DepartmentHistory] where DeptID = 3

So, you cannot delete data from temporal table.

WHAT ELSE YOU CANNOT DO

TRUNCATE TABLE

1.       You also cannot TRUNCATE the system versioned and the temporal table. You will get the following error;

Msg 13545, Level 16, State 1, Line 35

Truncate failed on table 'Test16.dbo.Department' because it is not supported operation on system-versioned tables.

2.       You cannot change the schema of the temporal table.

3.       You cannot add trigger to temporal table

YOU CAN ADD INDEX

You can index the temporal table and the main table as per your requirement.

SQL Server 2016 T-SQL Enhancements

Few enhancements are worth discussing.

• DROP IF EXISTS

In old versions of SQL Server we had to check if the table or column exists before dropping it. In SQL Server 2016, this is made simple by making IF EXISTS optional part of DROP TABLE, DROP INDEX, etc.

DROP TABLE IF EXISTS dbo.Product

DROP TRIGGER IF EXISTS trProductInsert

DROP INDEX IF EXISTS Department.DeptID.trProductInsert

It works with the following objects;

AGGREGATE	PROCEDURE	TABLE
ASSEMBLY	ROLE	TRIGGER
VIEW	RULE	TYPE
DATABASE	SCHEMA	USER
DEFAULT	SECURITY POLICY	VIEW
FUNCTION	SEQUENCE
INDEX	SYNONYM

For column use the following syntax;

ALTER TABLE Department DROP COLUMN IF EXISTS abc

• STRING_SPLIT

Prior to SQL Server, we had to create a Table value function to get the list of character separated values. MS have now made it the part of SQL Server built-in functions.

Select * from STRING_SPLIT('a,b,c',',')

Also works with the variable;

DECLARE @tags NVARCHAR(400) = a,b,,c,d' 

 

SELECT value 

FROM STRING_SPLIT(@tags, ',') 

WHERE RTRIM(value) <> '' 

Returns values are nvarchar if the input in nvarchar or nchar. Otherwise returns varchar. It accepts a single character as separator.

• SYSTEM_TIME

For system versioned tables, we can check the history values in the query;

SELECT * FROM dbo.Department

FOR SYSTEM_TIME BETWEEN '2017-02-08' And '2017-02-10';

The above query will give you all the updates made on the tblDepartment (with system versioning enabled) between the two given dates. You can also specify the date and time;

SELECT * FROM dbo.Department

FOR SYSTEM_TIME BETWEEN '2017-02-08 09:00:00.000' And '2017-02-10 10:00:00.000';

We will discuss the system version tables in another article.

• JSON

Create JSON string on the fly;

Select *

from Department

FOR JSON Path

Added functions are;

OPENJSON:

DECLARE @JSON NVARCHAR(4000)

SET @json = N'[{"DeptID":1,"DeptName":"Dept 1","ManagerID":1,"SysStartTime":"2017-02-09T08:54:49.6660045","SysEndTime":"9999-12-31T23:59:59.9999999"},{"DeptID":2,"DeptName":"D2","ManagerID":1,"SysStartTime":"2017-02-09T08:54:18.6513263","SysEndTime":"9999-12-31T23:59:59.9999999"},{"DeptID":3,"DeptName":"D3","ManagerID":1,"SysStartTime":"2017-02-09T09:03:01.9862457","SysEndTime":"9999-12-31T23:59:59.9999999"}]';

SELECT *

FROM OPENJSON( @json) a

We can also work with multiple records in a json string. In order to link the data in Jason strings to its respective Jason string;

SELECT *

FROM OPENJSON( @json) a

Cross Apply OPENJSON( a.value)

As you can see in the upper query, the cross apply is used to parse the complete data in the JSON string with multiple records.

Cycle Clipboard Ring SQL Server 2016

We have a new feature “Cycle Clipboard ring” in SQL Server management studio SSMS 13.0.16100.1. This new feature of SSMS allows you to toggle between what you have copied on the clipboard.

For example, if you copy something from one query window;

Use  DBNAME

Go

select * from Table1

Then copy another command from another query window;

Use  DBNAME2

Go

select * from TableName2

Now open a third query window and press CTRL+SHIFT+V; you will see one of the items from above. Keep holding CTRL+SHIFT and re-press “V” and you will see other item from the clipboard. This happens as the anything copied on the clipboard, it goes into the ring and it keeps rotating with the key combination on CTRL+SHIFT+V.

PS: Anything copied outside from SSMS before opening SSMS is also the part of this ring.