New T-SQL Commands in SQL Server 2017

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}', '[]{}', '()()');



Array

No comments yet... Be the first to leave a reply!