SQL Server 2016: T-SQL Enhancements

There are several SQL Server 2016 enhancements which are worth discussing.

Topics

1. 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, which is 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:

For column use the following syntax:

ALTER TABLE  Department DROP  COLUMN IF EXISTS abc

2. 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) <> ''

Returned values are nvarchar if the input is nvarchar or nchar. Otherwise, returns varchar. It accepts a single character as a separator.

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

4. 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 JSON strings to its respective JSON 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.