Thursday, February 15, 2018

TSQL enhancements in recent SQL version

String_AGG

This new function helps to generate comma separated string from the contents of a column from several records in a single string value


It was previously possible through XML trick - 

select stuff((select ‘,’ + [name] as [text()] 
       from names for xml path()),1,1,)

which will be easily converted to below using string_agg

select string_agg(cast(Name as varchar(Max)), ‘,’) From User

Trim

This new function has been requested for a lot of developers for a long time.
Removing the empty spaces in a string always demanded the use of two functions, like this:
SELECT RTRIM(LTRIM( ‘     test    ‘)) AS Result;
This new function simplifies this task:
SELECT TRIM( ‘     test    ‘) AS Result;

Translate

Translate does the work of several replace functions, simplifying some queries. 

Using ‘Replace’ function the transformation would be like this:
select replace(replace(replace(‘[137.4, 72.3]’,‘[‘,‘(‘),‘,’,‘ ‘),‘]’,‘)’) as Point
Using the ‘Translate’ function the transformations becomes way simpler:
SELECT TRANSLATE(‘[137.4, 72.3]’ , ‘[,]’, ‘( )’) AS Point, 
       TRANSLATE(‘(137.4 72.3)’ , ‘( )’, ‘[,]’) AS Coordinates

IIF

The IIF function is another function Access supports. It accepts as first input a predicate, as second input an expression to return in case the predicate is true, and as third input an expression to return in case the input is false or unknown. Here’s an example for using the function:
SELECT IIF(1 = 2, 'a', 'b') AS iif_result; 

iif_result

----------

b

CONCAT


The CONCAT function concatenates the input values into a single result string.  The concatenation operator + yields a NULL on NULL input. The CONCAT function 
converts NULL inputs to empty strings before concatenation.


FORMAT

The FORMAT function allows you to format an input value to a character string based on a .NET format string. You can optionally indicate a culture when relevant. As an example, the following code formats the result of the GETDATE() function using the format string 'd' (meaning, short date pattern), in one case using US English culture, and in another Japanese:
SELECT FORMAT(GETDATE(), 'd', 'en-US') AS us, FORMAT(GETDATE(), 'd', 'ja-JP') AS jp


This function allows a lot of flexibility in formatting inputs. For example, the following code formats product IDs as 10-digit strings with leading zeros:
SELECT FORMAT(productid, '0000000000') AS strproductid, productname

FROM Production.Products;


DROP IF EXISTS

One of the handiest T-SQL enhancements with SQL Server 2016 is the new DROP IF EXITS statement. It lets you check if an object exists before dropping it enabling you to skip wiring all those IF EXISTS blocks that we all write in out T-SQL scripts. You can see an example below.
DROP TABLE IF EXISTS [dbo].[MyTable];

STRING_SPLIT Function

STRING_SPLIT is another handy new function that makes it easy to parse strings based on a delimiter. The string can be a varchar, nvarchar, char, or nchar data type.
DECLARE @string varchar(100) = 'Richard, Mike, Mark'

SELECT value FROM string_split(@string, ',')


FOR JSON Clause

Likewise, the FOR JSON clause was add to enable you to convert tabular data to JSON. Unlike XML which has a native data type, the new JSON support is enabled by using the NVARCHAR data type. You can see an example of using the new FOR JSON clause in the following listing:
SELECT object_id, name FROM sys.tables FOR JSON PATH

JSON Functions

  • ISJSON -- The ISJSON function tests whether a string contains valid JSON.
  • JSON_VALUE -- The JSON_VALUE function extracts a scalar value from a JSON string.
  • JSON_QUERY -- The JSON_QUERY function extracts an object or an array from a JSON string.
  • JSON_MODIFY -- The JSON_MODIFY function updates the value of a property in a JSON string and returns the updated JSON string.

OPENJON Function

The OPENJSON table-value function (TVF) queries JSON text and can locate an array of JSON objects or iterate through the elements of array and generate a rowset from the results. You can see an example of using the OPENJSON function below.
DECLARE @JSON NVARCHAR(100)

SET @json = N'[ null, "string", 1, [true, false], ["a","b","c"], {"obj1":"obj2"} ]'; 

SELECT * FROM OPENJSON( @json )