Top 5 Interesting Features in SQL Server 2017

Microsoft has launched the most recent SQL Server 2017 release candidate (RC1, July 2017). It can be downloaded from this link. SQL Server 2017 will run on both Windows as well as on Linux OS. It also supports macOS via Docker too. In this post, we will discuss the new features of SQL Server 2017.

SQL Server 2017 New Features (SQL Server vNext)

Though the SQL Server 2017 has many new features, in this post, we are going to highlight the features which can be mostly used by SQL Server Developers.

1. SQL Server Machine Learning Services – R and Python

SQL Server 2016 integrated the R programming which can be run within the database server and can be embedded into T-SQL script too. Now, in SQL Server 2017, we can execute the Python script within the database server itself. Both, R and Python are most popular programming language which provides extensive support for data analytics along with natural language processing capability. R and Python are very powerful and are used by data scientists in their day to day tasks.

Adding Python to the SQL Server 2017 will provide more power to SQL Server for statistical computing, advanced analytics, and easy data transformations capabilities.

2. Improved SELECT INTO statement

Earlier, we were not able to provide the filegroup name on which we want to create the new table using ON keyword with SELECT INTO statement. The table gets created on the default filegroup of the user by default. However, in SQL Server 2017, now, we can provide the name of the filegroup on which we want to create the new table using SELECT INTO statement.

3. New String Functions

SQL Server 2017 has introduced several string functions which can help the T-SQL developers in their day to day task. Let’s discuss few important string functions here.

CONCAT_WS

SQL Server 2012 introduced the CONCAT function which can concatenate a variable number of strings passed as an argument to the function. However, in the case of NULL values, if we are using any separator, it gets repeated in the output.

CONCAT_WS function accepts the separator with a variable number of arguments and in the case of NULL values, the separator character is not repeated. Let’s have a look at the output of both these functions:

--CONCAT function
SELECT CONCAT('FirstName', ' - ', NULL, ' - ', 'LastName')
--Output
--FirstName - - LastName
 
--CONCAT_WS function
SELECT CONCAT_WS('-', 'FirstName', NULL, 'LastName')
--Output
--FirstName - LastName

In above example, we can see that the hyphen is not being repeated in the case of NULL values with CONCAT_WS function as like CONCAT function.

TRIM function

Earlier we were using RTRIM and LTRIM functions together to remove the trailing and leading spaces from a given string respectively. It makes the code longer.

Now, we can use a TRIM function which can remove the spaces from both ends of the string.

--With RTRIM and LTRIM
SELECT RTRIM(LTRIM(' String with spaces '))
--Output
--String with spaces
 
--TRIM function
SELECT TRIM(' String with spaces ')
--Output
--String with spaces

TRANSLATE function

Translate function can be used in place of using REPLACE function multiple times. The syntax for using TRANSLATE function is as below:

TRANSLATE (inputString, characters, translations)

The characters parameter is the array of characters to be replaced by translations parameters. The length of characters and translations parameters should be equal.

--Using nested REPLACE function
SELECT SELECT  REPLACE( REPLACE( REPLACE( REPLACE( REPLACE('abacbdcea', 'a', '1') , 'b', '2') ,'c', '3') ,'d', '4') ,'e', '5')
--Output
--121324351
 
--Using TRANSLATE function
SELECT TRANSLATE('abacbdcea', 'abcde', '12345')
--Output
--121324351

4. Resumable online index rebuild operation

An online Index rebuild operation can be started from the point of failure now which can be occurred due to the insufficient disk space.

5. New dynamic management views (DMVs)

1. sys.dm_db_log_stats – This view exposes summary level attributes and information on transaction log files.
2. sys.dm_tran_version_store_space_usage – This view tracks version store usage per database.
3. sys.dm_db_log_info – This view exposes VLF information to monitor, alert, and avert potential transaction log issues.
4. sys.dm_db_stats_histogram – This is a new dynamic management view for examining statistics.
5. sys.dm_os_host_info – This view provides operating system information for both Windows and Linux.

Thanks for the reading. Please share your inputs.