NEW Features of SQL Server

DROPIFEXISTS:

A new syntax has been introduced to check if an object exists before dropping it. Previously, if you wanted to check if a procedure existed before you dropped it, you had to write a statement like this
/****** Object:  StoredProcedure [its].[updQueueItemEmailStatus]    ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[its].[updQueueItemEmailStatus]') AND type in (N'P', N'PC'))
BEGIN
DROP PROCEDURE [its].[updQueueItemEmailStatus]
END
GO

Now with 2016 you can write this as

Syntax:
DROP OBJECT_TYPE [ IF EXISTS ] OBJECT_NAME
IF EXISTS: It is an optional clause and if it is mentioned in the DROP statement then it checks the existence of the object, if it exists it will drop otherwise continues executing the next statement in the block without raising any issues.


DROP PROCEDURE IF EXISTS [its].[updQueueItemEmailStatus]

Note: Now if the stored procedure does not exists it won't throw any Error
If the stored procedure doesn’t exists it will not raise any error, it will continue executing the next statement in the batch. Let’s try to re-drop the stored procedure WelcomeMessage which is already dropped.


Example:

If the table doesn’t exists it will not raise any error, it will continue executing the next statement in the batch. Let’s try to re-drop the Table dbo.Customers which is already dropped.

If the Database doesn’t exists it will not raise any error, it will continue executing the next statement in the batch. Let’s try to re-drop the Database SqlHintsDemoDB which is already dropped.

In the similar way we can drop Schema And Views


2 JSON support

Similar like XML, SQL Server now supports the JSON format. You can for example convert tabular data to JSON using the FOR JSON clause. An example:

SELECT NAME,first_name,entity_key,name_key FROM its_name
FOR JSON AUTO


Output:

OPENJSON - OPENJSON is a table-value function, which accepts some text which includes a JSON value and returns rows in the required format. FOR JSON, converts the rows into JSON format, but OPENJSON will convert back the JSON text into a table of rows.

IsJSON (< JSON text >) - This function verifies, whether the given text is formatted according to JSON standards/rules.

JSON_Value () - This is a scalar function and it parses the JSON text then extracts a value if it exists in the path arguments. This functionality is similar to 

XPATH (XML Path) which is used for selecting nodes from XML text. In addition, XPath may be used to compute values (e.g. strings, numbers or Boolean values) from the content of an XML document.

FOR JSON PATH – We can define the JSON structure in the query by defining column alias with a dot (.) separated value (for example ‘Root.level1’). This Functionality is similar to FOR XML PATH.

Example 1: Basic FOR JSON PATH example

SELECT 'Uday' FirstName, 'Kumar' LastName ,'Rajakonda' SurName

FOR JSON PATH

Output:

[{"FirstName":"Uday","LastName":"Kumar","SurName":"Rajakonda"}]

Example 2 :Basic FOR JSON AUTO requires at-least one table for generating the JSON output

SELECT 'Uday' FirstName, 'Kumar' LastName ,'Rajakonda' SurName

FOR JSON AUTO

RESULT:

Msg 13600, Level 16, State 1, Line 10

FOR JSON AUTO requires at least one table for generating JSON objects. Use FOR JSON PATH or add a FROM clause with a table name.

From the above result it is clear that the FOR JSON AUTO clause works only if at-least one table is mentioned in the from clause.

Example 3: FOR JSON PATH/AUTO example where column names are not specified in the select list, instead * is mentioned

Example 4: FOR JSON PATH/AUTO example where required columns in the JSON output are specified in the SELECT clause

PRINT '******* FOR JSON PATH output *******'
SELECT top 5 name,name_suffix_key,entity_key FROM its_name  FOR JSON PATH
GO
PRINT '******* FOR JSON AUTO output *******'
SELECT top 5 name,name_suffix_key,entity_key FROM its_name FOR JSON AUTO

Output:

Example 5: To include NULL values in the JSON output, we need to specify the property INCLUDE_NULL_VALUES in the FOR JSON clause

Note: If this option is not specified, in case of NULL value the name-value pair will be removed from the JSON output  

PRINT '******* FOR JSON PATH output *******'

SELECT top 5 name,name_suffix_key,entity_key FROM its_name  FOR JSON PATH,INCLUDE_NULL_VALUES

GO

PRINT '******* FOR JSON AUTO output *******'

SELECT top 5 name,name_suffix_key,entity_key FROM its_name FOR JSON AUTO,INCLUDE_NULL_VALUES

Output: 

Example 6 :We can use the ROOT option in the FOR JSON clause to generate a wrapper object around the generated JSON output. In the below example the ROOT option creates a Customers JSON wrapper object around the generated JSON output:

PRINT '******* FOR JSON PATH output *******'

SELECT top 5 name,name_suffix_key,entity_key FROM its_name  FOR JSON PATH,INCLUDE_NULL_VALUES,ROOT('Names')

GO

PRINT '******* FOR JSON AUTO output *******'

SELECT top 5 name,name_suffix_key,entity_key FROM its_name FOR JSON AUTO,INCLUDE_NULL_VALUES,ROOT('Names')

Example 7: In case of FOR JSON PATH clause using “.” Symbol in the column aliases, we can name the each object in the resultant JSON array as shown below:

SELECT   name_key[Name.namekey],name[Name.name] FROM ITS_NAME FOR JSON PATH,INCLUDE_NULL_VALUES,ROOT('Names')

Output:

Using “.” symbol in the column aliases doesn’t have any effect in the resulting JSON output in case of FOR JSON AUTO as shown below:


using JSON AUTO


SELECT   name_key[Name.namekey],name[Name.name] FROM ITS_NAME FOR JSON Auto,INCLUDE_NULL_VALUES,ROOT('Names')


Output:

Example 8: We can convert each row into a JSON object with multiple sub-objects by using “.” Symbol in the column alias as shown below:

SELECT   name_key[Name.namekey],name[Name.name],entity_key[Entity.entityKey] FROM ITS_NAME FOR JSON PATH,INCLUDE_NULL_VALUES,ROOT('Names')

OutPut:

3 FORMATMESSAGE

FROM 2016, We can supply our own String
Declare @str varchar(max)
set @str=FORMATMESSAGE('Hello %s,Uday %s' ,'RajaKonda', 'Kumar')
select @str

4 STRING_SPLIT

In previous release we used to write lot of code ( we used to create a function to split the string).
Old way of writing code
Example
DECLARE @string VARCHAR(800) = '001,002,003,004'
SELECT * FROM [dbo].[fnSplitString] (@string,',')

But with sql server 2016 we can write as
DECLARE @string VARCHAR(800) = '001,002,003,004'
SELECT * FROM STRING_SPLIT (@string,',')


Output: