Even though SQL Server 2016 has been out for a while now, and I’m sure there are plenty of articles on the new features that are available, I thought that I would write about are the features that I particularly like.

Note that the outputs shown below are from the full script at the bottom of this page.

DROP .. IF EXISTS

Of all the new features in 2016, I would probably use this one the most, as it is much more elegant than the IF EXISTS... DROP.

DROP TABLE IF EXISTS dbo.TestTable2016
GO

STRING_AGG

Probably the coolest feature for me is not having to use the old STUFF... FOR XML to create CSV data. Now we can do it using this simple aggregate function.

-- STRING_AGG concatenates rows together with a separator
SELECT STRING_AGG(col1, ',') AS AllRowsAsOneCSVRow
FROM dbo.TestTable2016

Because this is an aggregate function, you will need a GROUP BY clause for additional columns, but that does make sense.

Edit 2022-01-21: Size Limits

STRING_AGG aggregation result exceeded the limit of 8000 bytes. Use LOB types to avoid result truncation.

Today I got the above error for a Procedure that has been around for some time.

Turns out that the output type of STRING_AGG is dependant on the input type. Because my input type is a VARCHAR(50) the output is a VARCHAR(8000) (it’s worse for an NVARCHAR).

The fix is to cast the field being aggregated, like so…

SELECT STRING_AGG(CAST(col1 AS NVARCHAR(MAX)), ',') AS AllRowsAsOneCSVRow
FROM dbo.TestTable2016

For more see the SQL Docs – STRING_AGG (Transact-SQL)

CONCAT_WS (Concatenate With Separator)

I find this feature particularly handy with concatenating things like addresses and CSV rows.

Null’s are ignored, i.e. no double separator.

-- CONCAT_WS concatenates fields together with a separator
SELECT CONCAT_WS(',', col1, col2, col3) ColumnsIntoCSVRows
FROM dbo.TestTable2016

FOR JSON

Ok, so this might be even cooler than STRIN_AGG, I’m just not quite certain where I can use it. JSON gets used all the time in transferring data, but not directly from SQL. It is however another tool in our toolbelts and we’ll just have to wait for an opportunity.

Note, I did get somewhat of an opportunity to work with JSON in this article Querying a JSON Array in SQL, although, not the functions listed here.

-- Convert data to JSON objects
SELECT * FROM dbo.TestTable2016 FOR JSON AUTO

I haven’t yet worked out how the column name is determined, it doesn’t seem to be based on the data, nor connection. If you know, let me know in the comments.

If you want to alias the column then just wrap it in a Select, like this…

SELECT (SELECT * FROM dbo.TestTable2016 FOR JSON AUTO) AS JsonData

FOR JSON – Nested Objects

I could probably write a whole post on the FOR JSON function, but I’ll stop with this…

If you want to crate objects within your objects then all you have to do is put the namespace (a dot separator) in your column alias, like so…

SELECT
     ROW_NUMBER() OVER(ORDER BY col1 ASC) as id,
     col1 AS name,
     col2 as [data.col2],
     col3 as [data.col3]
 FROM dbo.TestTable2016
 FOR JSON PATH

[{
         "id": 1,
         "data": {
             "col2": "b2",
             "col3": "b3"
         }
     }, {
         "id": 2,
         "name": "a1",
         "data": {
             "col2": "a2#a,a2#b",
             "col3": "a3"
         }
     }, {
         "id": 3,
         "name": "c1",
         "data": {
             "col2": "c2"
         }
     }
 ]

TRANSLATE

This feature does a single character replace with many characters, that is, the characters (parm 2) and translations (param 3) are essentially arrays of characters rather than words. So it simply does a multi replace of characters.

The 2nd and 3rd parameters need to be the same length, which means removing characters still needs to be done the old way.

-- TRANSLATE is a multiple replace, i.e. replace all these with all these
SELECT col2 AS RawData, TRANSLATE(col2, '#,', '*-') AS Translated
FROM dbo.TestTable2016

I’ve never actually used this feature, but it does look cool.

All Scripts together

/* Coolest new language features in SQL 2016 */
-- DROP .. IF EXISTS
DROP TABLE IF EXISTS dbo.TestTable2016
GO

-- set up test table
CREATE TABLE dbo.TestTable2016
(
       col1 VARCHAR(50) NULL,
       col2 VARCHAR(50) NULL,
       col3 VARCHAR(50) NULL
)
INSERT INTO dbo.TestTable2016 (col1, col2, col3 )
VALUES
       ('a1', 'a2#a,a2#b',  'a3'),
       (NULL, 'b2',  'b3'),
       ('c1', 'c2', NULL)

-- CONCAT_WS concats fields together with a separator
SELECT CONCAT_WS(',', col1, col2, col3) ColumnsIntoCSVRows
FROM dbo.TestTable2016

-- STRING_AGG concats rows together with a separator
-- replaces the STUFF method
SELECT STRING_AGG(col1, ',') AS AllRowsAsOneCSVRow
FROM dbo.TestTable2016

-- Note that if you want other fields (which you will), you'll need to add a GROUP BY
-- Also note that if you want other fields
-- TRANSLATE is a multiple replace, i.e. replace all these with all these
SELECT CONCAT_WS(',', col1, TRANSLATE(col2, '#,', '*-'), col3)
FROM dbo.TestTable2016

-- Convert data to JSON objects
SELECT (SELECT * FROM dbo.TestTable2016 FOR JSON AUTO) AS JsonData

-- clean up
DROP TABLE dbo.TestTable2016