Finally we have new version 2017 with Linux support
Adding Python to the SQL Server 2017 will provide more power to SQL Server for statistical computing, advanced analytics, and easy data transformations capabilities.
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:
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.
Now, we can use a TRIM function which can remove the spaces from both ends of the string.
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.
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:
1
2
3
4
5
6
7
8
9
| --CONCAT function SELECT CONCAT('FirstName', ' - ', NULL, ' - ', 'LastName') --Output --FirstName - - LastName--CONCAT_WS function SELECT CONCAT_WS('-', 'FirstName', NULL, 'LastName') --Output --FirstName - LastName |
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.
1
2
3
4
5
6
7
8
9
| --With RTRIM and LTRIMSELECT RTRIM(LTRIM(' String with spaces '))--Output--String with spaces--TRIM functionSELECT 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.
1
2
3
4
5
6
7
8
9
| --Using nested REPLACE functionSELECT SELECT REPLACE( REPLACE( REPLACE( REPLACE( REPLACE('abacbdcea', 'a', '1') , 'b', '2') ,'c', '3') ,'d', '4') ,'e', '5')--Output--121324351--Using TRANSLATE functionSELECT 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)
- sys.dm_db_log_stats – This view exposes summary level attributes and information on transaction log files.
- sys.dm_tran_version_store_space_usage – This view tracks version store usage per database.
- sys.dm_db_log_info – This view exposes VLF information to monitor, alert, and avert potential transaction log issues.
- sys.dm_db_stats_histogram – This is a new dynamic management view for examining statistics.
- sys.dm_os_host_info – This view provides operating system information for both Windows and Linux.
