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.