Blog

T-SQL features you might have missed - Part 2

  • 21 January 2019
  • 0 replies
  • 125 views
T-SQL features you might have missed - Part 2
Userlevel 5
Badge +2

SQL Server 2012

In case you missed part 1 of the new features.

Window function frames

This feature is a really cool enhancement to window functions which fills a niche gap for queries of a cumulative nature. A window function can be applied on a limited set in regards
to the partitioning, the ordering and the current row.

Other language features

The parse function has also been added which can be used to parse data using culture-specific settings.

The try_parse function will provide null-values when this fails instead of causing an error.

New functions such as datefromparts, timefromparts make it very easy to create a date or time object by providing years, months, days, minutes, etc. instead of a date or time
string


The eomonth function provides you with the last day of the month based on a date in this month. It is possible to provide an extra parameter which


SQL Server 2014

SQL Server 2014 brought a lot of new cool features and performance to the table, but the T-SQL language hasn’t really changed. Nothing to report here!

SQL Server 2016

This SQL Server version saw great improvement for features added in SQL Server 2016 and a few cool language features.
JSON functions
DROP IF EXISTS
SESSION_CONTEXT

JSON support

SQL Server 2016 has extensive support for JSON processing, similiar to XML processing. JSON does not have its own datatype in contrast to XML. It’s always just text.


The result would look something like this:


The openjson clause provides you with access to the JSON data in tabular form. Sets of data can be accessed using the index, can be returned as rows and can have their properties named using a with-clause.

There are also various functions to access a value directly and check beforehand whether or not the JSON is correctly formed.


If you want to use special characters in json, they need to be escaped properly. This will be done automatically when using the for json clauses but if this ever needs to be done manually, use the string_escape function.


Drop if exists

Mostly very usable for the Software Factory once the minimal version is SQL Server 2016. This feature allows the developer to drop an object only if it exists. As a Software Factory
developer, you probably won’t have to use this often.


The session_context function

A better alternative to the context_info() function, the session_context() function provides options to store more data than just the user name in a session. This feature can already
be leveraged when using session variables.

SQL Server 2017

Quite a few new T-SQL language features, and the features that have been added are pretty nifty.
TRIM
STRING_AGG
CONCAT_WS
TRANSLATE

Trim-function

Yep, trim. Now you won’t have to write ltrim(rtrim(x)) but you can use trim(x).
Cool thing is, besides spaces you can also tell the function to trim other characters!


The string_agg function

Finally! Aggregating strings over a set without having to use FOR XML!
The string_agg function has two parameters, one indicating the column that should be aggregated and one for a separator between each item. It’s not allowed to use an order by on columns that are not being grouped, so the order of the items in the string_agg function can be a bit random. To solve this problem, provide a specific order by for the items in the string_agg using the within group (order by x) clause. The following example shows the different results.



The concat_ws function

You probably won’t use it a
lot. Concat_ws is similar to the concat function mentioned earlier, but allows
for a separator between values (ws stands for ‘with separator’). Could be nice
for building an address from individual parts or something.


Translate function
This function has existed for along, long time in many SQL languages, but not in T-SQL until now. Ever wrote a query with a bunchof nested replace() functions to replace different characters with othercharacters? The translate function makes this a lot more easy.


Azure

Generally, Azure is compatible with the language features of the most recent SQL Server version. However, Azure does have it’s own versioning so there might be minor differences. Please check the official documentation on T-SQL differences, feature differences and how to resolve these differences.

0 replies

Be the first to reply!

Reply