Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

T-SQL Enhancements in SQL Server 2017


By:   |   Read Comments   |   Related Tips: More > SQL Server 2017

Attend this free live MSSQLTips webcast

Leveraging Storage Spaces Direct for SQL Server High Availability

Thursday, July 19, 2018 - click here to learn more


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:

create or alter

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):

no create or alter in SSMS

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:

allowed query hints

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:

WWI DW filegroups

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

SELECT INTO ON

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.

concat example

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):

concat_ws example

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:

dirty string

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:

clean string with TRANSLATE

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:

ltrim vs rtrim vs trim

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.

trim with additional characters

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:

string_agg example

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

string_agg with integers

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.

string_agg with within group

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.


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools