T-SQL Enhancements in SQL Server 2017

By: Koen Verbeeck | Comments | Related: > SQL Server 2017

Problem

With the release of SQL Server 2017, there were some enhancements introduced for the T-SQL language. In this tip, we’ll give an overview of the new features along with a demonstration of how to use them. We will also provide you with references to any existing tips for more details.

Solution

This tip only covers enhancements to traditional T-SQL for the database engine. Changes for graph databases are not included in this overview.

SQL Server CREATE OR ALTER

SQL Server 2017 also includes the new CREATE or ALTER feature added in the first service pack of SQL Server 2016.

The CREATE OR ALTER syntax can be used for views, procedures, functions and triggers.

The following T-SQL statement either creates or alters a view in the WideWorldImporters data warehouse:

This means you can run the statement multiple times without an issue. For the moment, SQL Server Management Studio (SSMS) doesn’t allow you to script this (DROP AND CREATE might be an alternative):

You can find more information about CREATE OR ALTER in the tip New CREATE OR ALTER statement in SQL Server 2016 SP1.

SQL Server Optimizer Hints

SQL Server 2017 also includes the new construct for optimizer hints added in the first service pack of SQL Server 2016.

A more generic construct for specifying query hints has been introduced: OPTION (USE HINT (‘hint1’, ‘hint2’, …)). You can find more information in the official documentation.

Regarding the query hint construct; you can find all allowed hints for this construct through the sys.dm_exec_valid_use_hints DMV:

SQL Server SELECT INTO and Filegroups

The SELECT INTO clause allows you to easily create a table using the schema of a result set returned by SELECT. However, in previous versions the newly created table was assigned to the default filegroup. With SQL Server 2017, we can now assign a non-default filegroup to the table when it’s created.

To demonstrate this, we can use the WideWorldImporters data warehouse, where the filegroup USERDATA has already been added:

With the following statement, we create a new table in the USERDATA filegroup:

Important to note is the ON clause comes before the FROM clause, otherwise it will be mistaken with the ON clause from a JOIN. Interesting is also that SSMS Intellisense doesn’t recognize the new construct yet.

The tip SELECT...INTO Enhancements in SQL Server 2017 gives a more detailed example of the new feature.

SQL Server CONCAT_WS function

The CONCAT_WS function is an enhancement of the already existing CONCAT function, which was introduced in SQL Server 2012. CONCAT allows you to concentrate multiple expressions into one string, where NULL values are treated as an empty string.

However, in many cases the function requires you to type a separator yourself (a single space in the example above). The CONCAT_WS function eliminates this requirement by giving you the option to specify the separator separately (hence WS: with separator):

SQL Server TRANSLATE and TRIM functions

TRANSLATE is in reality a way to replace multiple REPLACE functions at once. The syntax is as follows:

TRANSLATE(inputString,characters,replacements)

The length of the characters expression should be the same as the replacements expression. Let’s illustrate the use of TRANSLATE by cleaning up some text. The following variable contains some non-printable characters:

Normally, to get rid of all those characters you would need to use several REPLACE function calls. LTRIM or RTIM can’t be used, as those characters are in the middle of the text. With TRANSLATE, we can easily remove or replace all the non-wanted characters in one single function call:

In the example, we replace all the non-printable characters with a special symbol of our choosing: the pipe symbol. After that, we need only one REPLACE function call to replace the pipes with the empty string, returning cleaned-up text string.

Speaking of cleaning up text, another new function is the TRIM function, which is the combination of the LTRIM and RTRIM functions. The following script shows how these functions are used:

An extra exclamation mark is added to the variable when calling PRINT, to show the possible trailing spaces. Some extra functionality has been added to the TRIM function though, in contrast with LTRIM and RTRIM. You can specify extra characters that need to be removed from the start and the end of the text.

The difference between TRANSLATE and TRIM is that TRIM will only replace characters at the start and the end of the text, while TRANSLATE will search through the entire text. TRIM will also replace characters by the empty string while TRANSLATE will replace characters by other characters specified in the function call.

SQL Server STRING_AGG function

The new STRING_AGG function is the reverse of the STRING_SPLIT function, which was introduced in SQL Server 2016. STRING_AGG concatenates string values using a separator. Let’s illustrate with an example using the AdventureWorks2017 data warehouse. For each product category, we list a concatenated string with all its related product subcategories:

Notice you can also input non-text data types, these will be converted to NVARCHAR.

STRING_AGG doesn’t add the separator at the end of the string. By default, the concatenated values are sorted ascending. You can change the sorting order with the WITHIN GROUP clause.

You can also use other expressions to sort the data:

string_agg with within group with other expression

Keep in mind NULL values are ignored by the function. STRING_AGG provides us with a nice built-in method to concatenate string values over rows. In previous versions, this required a bit more coding and some solutions had performance issues over large data sets. You can find a nice overview of different solutions in the article Concatenating Row Values in Transact-SQL. Aaron Bertrand also discusses a good use case for STRING_AGG in the tip Solve old problems with SQL Server’s new STRING_AGG and STRING_SPLIT functions.

New SQL Server Bulk Access Methods

For BULK INSERT, CSV files are now supported. You can specify FORMAT = ‘CSV’ and use FIELDQUOTE to specify a quote character other than the double quote (“). You can also use Azure Blob Storage for data files, format files and error files. The same additions are added for OPENROWSET.

Next Steps

The official overview can be found here.
Aaron Bertrand has a good overview of all the database engine changes in SQL Server 2017.
You can find more SQL Server 2017 tips in this overview.

About the author

Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips