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:

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

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.