New syntax in SQL 2016 (+ vNext)
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 even fully migrated to it on production environments (thumbs up!)
But did you know the existing of following new syntax in SQL 2016 or vNext? There is more than what I will discuss in this post, but its a good starter.
(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)
[code lang=”sql”]INSERT INTO player (name, surname, info )
VALUES (N’Ovidiu’, N’Cracium’,
COMPRESS(N’{“sport”:”Tennis”,”age”: 28,”rank”:1,”points”:15258, turn”:17}’));
[/code]
[code lang=”sql”]SELECT _id, name, surname, datemodified,
CAST(DECOMPRESS(info) AS NVARCHAR(MAX)) AS info
FROM player;[/code]
- COMPRESS — https://msdn.microsoft.com/en-us/library/mt622775.aspx
- DECOMPRESS — https://msdn.microsoft.com/en-us/library/mt622776.aspx
DROP IF EXISTS
In all SQL versions before 2016 you had to do a check in the meta data if a table, procedure, column, constraint or trigger exists in case you want to create it again.
SQL 2016 made it easier for us with the DROP .. IF EXITS syntax
[code lang=”sql”]
— 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
[/code]
CREATE OR ALTER PROCEDURE
This might be syntax that a lot of people will be happy about. There was no way to have 1 script that creates and updates a SP without removing it first. But with the new CREATE OR ALTER PROCEDURE function its finally possible
[code lang=”sql”]
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
[/code]
TRUNCATE TABLE … WITH PARTITION
We all know that using the DELETE syntax is heavy on memory and tempdb. But 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. This will go off course faster then a DELETE.
[code lang=”sql”]TRUNCATE TABLE PartitionTable1
WITH (PARTITIONS (2, 4, 6 TO 8)); [/code]
JSON
JSON text is now fully supported. Extraction and creation.
[code lang=”sql”]
— 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
[/code]
A detailed description of what is possible with the JSON functionality in SQL can be found on TechNet
- JSON_VALUE — https://msdn.microsoft.com/en-us/library/dn921898.aspx
- JSON_QUERY — https://msdn.microsoft.com/en-us/library/dn921884.aspx
- JSON_MODIFY — https://msdn.microsoft.com/en-us/library/dn921892.aspx
- ISJSON — https://msdn.microsoft.com/en-us/library/dn921896.aspx
- OPENJSON — https://msdn.microsoft.com/en-us/library/dn921885.aspx
TRIM
Version: vNext
Finally! Yes, it’s here. Many people were asking for it. Now we have a string function that removes leading aswell as trailing spaces.
[code lang=”sql”]SELECT LTRIM(RTRIM(‘ Why do I need to use 2 functions?   ‘))[/code]
can be done now with
[code lang=”sql”]SELECT TRIM(‘ Why do I need to use 2 functions?   ‘)[/code]
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.
[code lang=”sql”]SELECT CONCAT_WS(‘,’,’1 Microsoft Way’, NULL, NULL, ‘Redmond’, ‘WA’, 98052) AS Address;[/code]
Result:
[code]1 Microsoft Way,Redmond,WA,98052[/code]
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.
SELECT STRING_AGG (FirstName, ', ') AS csv
FROM Person.Person;Result:
Tom, Patrick, Walter, Nick, Frederick, StefaanTRANSLATE
Version: vNext
Returns the string provided as a first argument after some characters specified in the second argument are translated into a destination set of characters.
[code lang=”sql”]SELECT TRANSLATE(‘a string value’, ‘abcdefghijklmnopqrstuvwxyz’, ‘zyxwvutsrqponmlkjihgfedcba’); — Result z hgirmt ezofv[/code]
STRING_SPLIT
I would not be shocked if 99% percent of database developers have a function on 1 or multiple databases to split a string into multiple rows. Now again we can say ‘finally’ this has been implemented as a default function in SQL 2016
[code lang=”sql”]SELECT *
FROM string_split(‘ABC,DEF,GHI,JKL,MNO,PQR,STU,VWX,YZA’, ‘,’)[/code]
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:
[code lang=”sql”]SELECT DATEDIFF(SECOND,’19000101', ‘29991231’)[/code]
The problem with this is, that it doesn’t work. the DATEDIFF function returns INT.
So it returns 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.
[code lang=”sql”]SELECT DATEDIFF_BIG(SECOND,’19000101', ‘29991231’)[/code]
