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 regardsto 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 Factorydeveloper, 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 alreadybe 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 alot. 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.
