Despite the maturity of TSQL, SQL Server 2017 still introduces several new features which to the T-SQL family which can be very useful in database development.
CONCAT_WS
The CONCAT command was first introduced with SQL Server 2012< to simplify joining multiple columns as below:
SELECT CONCAT(Title,' ' , FirstName, ' ' , MiddleName, ' ' , LastName) FullName FROM Person.Person
When above command is executed, Title, FirstName, MiddleName and LastName are joined together with the space as the separator.
However, in most of the cases, the separator is unique for the entire command. Therefore, it is little troublesome to type the separator multiple times. This can be done with the newly introduced CONCAT_WS command where the first parameter is the separator and is followed by the column list.
SELECT CONCAT_WS(' ' ,Title, FirstName , MiddleName ,
LastName) FullName FROM Person.Person
In this command, it is important to note that NULL values are handled in the same manner as CONCAT.
TRIM
Trim is one of the most popular feature requests by DBAs and developer. It is little hard to believe that Microsoft took this many years to introduced this valuable feature.
In the pre-SQL Server 2017 environments, you need to use RTRIM and LTRIM both as shown below.
SELECT RTRIM(LTRIM(' Sample Value '))
Following the introduction of TRIM in SQL Server 2017, this can be simplified to:
SELECT TRIM(' Sample Value ')
Though there are no performance improvements, this will improve usability.
STRING_AGG
Let us consider a simple scenario – assume every person can have one or many email addresses and there is a requirement to list the user name along with email addresses. If there are multiple email addresses for a user those should be separated by a comma. If you only need to get the count of email addresses, you can simply use the GROUP BY and COUNT command. However, this scenario is different as it requires aggregating strings. STRING_AGG is the command can be utilized to achieve this as shown in the below T-SQL statement:
SELECT p.lastname,STRING_AGG(e.emailaddress,',')
email
FROM person.person P INNER JOIN person.EmailAddress e
ON p.BusinessEntityID=e.BusinessEntityID
GROUP BY lastname
This statement will result in the below.
However, this also gives us following error.
Msg
9829, Level 16, State 1, Line 1
STRING_AGG
aggregation result exceeded the limit of 8000 bytes. Use LOB types to avoid
result truncation.
As explained in the error message, aggregation should not exceed limit of 8000 bytes, but by casting the column to much larger data type, in this case varchar(MAX) you can get the same results without generating an error.
SELECT p.lastname,STRING_AGG(CAST(e.emailaddress AS VARCHAR(MAX)),',') email
FROM person.person P INNER JOIN person.EmailAddress e
ON p.BusinessEntityID=e.BusinessEntityID
GROUP BY lastname
TRANSLATE
How many times you have used multiple REPLACE command in single T-SQL statement? Let us assume
5*[1+2]/{6-3}. This should be 5*91+2)/(6-3). If you use REPLACE function, this is the TSQL:
SELECT REPLACE(REPLACE(REPLACE(REPLACE('5*[1+2]/{6-3}','[','('), ']', ')'), '{', '('), '}', ')');
It is very difficult to maintain this code as it is not very readable. With SQL Server 2017, things are much easier now. You need to not provide in multiple REPLACE functions simply enter the characters in their placement order as shown in the below:
SELECT TRANSLATE('5*[1+2]/{6-3}', '[]{}', '()()');
]]>