New syntax in SQL 2016

SQL 2016 has been released now already since a couple of months. Many of us have been testing it, even developed applications on it, or fully migrated to it on production environments (thumbs up!). But did you know about the existence of following new syntax in SQL 2016 or vNext? There is more than what I will discuss in this post, but it’s a good start.

(DE)COMPRESS

When you have long text strings or binary data saved in your database and want to keep your database size low, then the new COMPRESS and DECOMPRESS function will be very handy. It compresses the given value with the ZIP method. Possible data types are: nvarchar(n), nvarchar(max), varchar(n), varchar(max), varbinary(n), varbinary(max), char(n), nchar(n), or binary(n)
Return data type is: varbinary(max)

[html]
INSERT INTO player (name, surname, info )  
VALUES (N'Ovidiu', N'Cracium',   
        COMPRESS(N'{"sport":"Tennis","age": 28,"rank":1,"points":15258, turn":17}'));  [/html]

[html]SELECT _id, name, surname, datemodified,</pre>

CAST(DECOMPRESS(info) AS NVARCHAR(MAX)) AS info
FROM player;[/html]

DROP IF EXISTS

In all SQL versions before 2016, you had to do a check in the meta data to see whether a table, procedure, column, constraint or trigger existed in case you wanted to create it again. SQL 2016 made it easier for us with the DROP .. IF EXITS syntax.

[html] --TABLES
IF OBJECT_ID('dbo.TableX') IS NOT NULL
DROP TABLE dbo.TableX
--Can be replaced with
DROP TABLE IF EXISTS dbo.TableX

--PROCEDURES
IF OBJECT_ID('dbo.sp_ProcedureX') IS NOT NULL
DROP PROCEDURE dbo.sp_ProcedureX
--Can be replaced with
DROP PROCEDURE IF EXISTS dbo.sp_ProcedureX

--TRIGGERS
IF OBJECT_ID('dob.tr_TriggerX') IS NOT NULL
DROP TRIGGER dbo.tr_TriggerX
--Can be replaced with
DROP TRIGGER IF EXISTS dbo.tr_TriggerX

--COLUMNS
IF EXISTS(SELECT * FROM sys.columns WHERE Name = N'ColumnB' AND OBJECT_ID = OBJECT_ID(N'dbo.TableX'))
ALTER TABLE dbo.TableX DROP COLUMN ColumnB
--Can be replaced with
ALTER TABLE dbo.TableX DROP COLUMN IF EXISTS ColumnB

--CONSTRAINTS
IF OBJECT_ID('dbo.DF_TableX_ColumnA', 'C') IS NOT NULL
ALTER TABLE dbo.TableX DROP CONSTRAINT DF_TableX_ColumnA
--Can be replaced with
ALTER TABLE dbo.TableX DROP CONSTRAINT IF EXISTS DF_TableX_ColumnA[/html]

CREATE OR ALTER PROCEDURE

This might be a syntax that a lot of people will be happy about. Previously, there was no way to have one script that created and updated a SP without removing it first. With the new CREATE OR ALTER PROCEDURE function its finally possible.

[html]IF OBJECT_ID('dbo.sp_ProcedureX') IS NOT NULL
DROP PROCEDURE dbo.sp_ProcedureX
GO

CREATE PROCEDURE dbo.sp_ProcedureX
AS
BEGIN
PRINT (1)
END

--Can be replaced by
CREATE OR ALTER PROCEDURE dbo.sp_ProcedureX
AS
BEGIN
PRINT (1)
END[/html]

TRUNCATE TABLE … WITH PARTITION

We all know that using the DELETE syntax is heavy on memory and tempdb. Before, it was the only option to remove for example a whole year of data from a FACT table. Now, there is an other option the TRUNCATE TABLE … WITH PARTITION. With this new functionality its possible to TRUNCATE a PARTITION, which of course will go faster then a DELETE.

[html]TRUNCATE TABLE PartitionTable1
WITH (PARTITIONS (2, 4, 6 TO 8));[/html]

JSON

JSON text is now fully supported. Extraction and creation.

[html] --Some example JSON data
DECLARE @json NVARCHAR(4000)
SET @json =
N'{
"info":{
"type":1,
"address":{
"town":"Bristol",
"county":"Avon",
"country":"England"
},
"tags":["Sport", "Water polo"]
},
"type":"Basic"
}'

--Extract single values
SELECT JSON_VALUE(@json, '$.info.address.town')

--Extract array of values
SELECT *
FROM OPENJSON(@json, '$.info.tags')

--Creating a JSON dataset
SELECT object_id, name
FROM sys.tables
FOR JSON PATH[/html]

A detailed description of what is possible with the JSON functionality in SQL can be found on TechNet

• JSON_VALUE
• JSON_QUERY
• JSON_MODIFY
• ISJSON
• OPENJSON

TRIM

Version: vNext
Finally! Yes, it’s here. Many people were asking for it. Now we have a string function that removes leading as well as trailing spaces.

[html]SELECT LTRIM(RTRIM(' Why do I need to use 2 functions?   '))[/html]

can be done now with

[html] SELECT TRIM(' Why do I need to use 2 functions?   ')[/html]

CONCAT_WS

Version: vNext
This function takes a variable number of arguments and concatenates them into a single string using the first argument as separator.

[html]SELECT CONCAT_WS(',','1 Microsoft Way', NULL, NULL, 'Redmond', 'WA', 98052) AS Address;[/html]

Result:

[html] 1 Microsoft Way,Redmond,WA,98052[/html]

STRING_AGG

Version: vNext
With this function that will come with vNext you can aggregate string column values with a specified seperator. Seperator will not added to last value.

[html]SELECT STRING_AGG (FirstName, ', ') AS csv
FROM Person.Person;[/html]

Result:

[html]Tom, Patrick, Walter, Nick, Frederick, Stefaan[/html]

TRANSLATE

Version: vNext
The translate function returns the string provided as a first argument after some characters specified in the second argument are translated into a destination set of characters.

[html]SELECT TRANSLATE('a string value', 'abcdefghijklmnopqrstuvwxyz', 'zyxwvutsrqponmlkjihgfedcba'); --Result z hgirmt ezofv[/html]

STRING_SPLIT

I would not be shocked if 99% percent of database developers have a function on one or multiple databases to split a string into multiple rows. Now, we can ‘finally’ say this has been implemented as a default function in SQL 2016.

[html]SELECT *
FROM string_split('ABC,DEF,GHI,JKL,MNO,PQR,STU,VWX,YZA', ',')[/html]

DATEDIFF_BIG

If you, for example, need to calculate the time difference in (seconds, nanosecond, …) between two dates that are way out of each other, then you would do this:

[html]SELECT DATEDIFF(SECOND,'19000101', '29991231')[/html]

The problem with this is, however, is that it doesn’t work. The DATEDIFF function returns INT, displaying the following error:

The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

This is fixed with a new function DATEDIFF_BIG.

[html]SELECT DATEDIFF_BIG(SECOND,'19000101', '29991231')[/html]