On 17th July 2017, Microsoft released the full & final Release Candidate 1 (RC1) version of SQL Server 2017.
SQL Server 2017, the next major release of the SQL Server database, has been available as a community preview. Microsoft provides most interesting and new features that bring the latest release of SQL Server.
While the SQL Server 2017 does not have detailed features of SQL Server 2016 but still, some new features will be added in it. One of the major advantages is SQL Server 2017 is that it will run on both Windows as well as on Linux OS. It also supports macOS via Docker too.
For those looking to do data science with data in SQL Server, there are a number of new features compared to SQL Server 2017 which might be of interest.
In this post, we will discuss the new features of SQL Server 2017.
With the next release of SQL Server there are lots of interesting new features as given below:
Support for Linux
The best-known new feature is support for Linux (RHEL, SUSE Enterprise Linux, and Ubuntu), and for containers running on Windows, Linux and macOS; that includes Always On availability groups for high availability integrated with native Linux clustering tools like Pacemaker.
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.
Python combines the script with a great readability and also used by IT admins, data scientists, developers, and data analysts. In addition, Python allows to easily leverage an external statistical package to perform basic data manipulation and statistical analysis. When the users combine this capability with Transact-SQL, the result becomes powerful.
Adaptive Query Processing (AQP)
At the past, SQL Server can rely on the index and column statistics to consider the features of query execution, like multiple operations and different physical join operators. There was a lot of guesswork here, even that perfect statistics, SQL Server has no knowledge of correlations between two columns in the specific query. The SQL Server engine supports adaptive query processing, ability to meet the users need. In other terms, results of the query optimizer can alter later steps within the plan, probably better. Some plans are cached plans, notable the actually granted memory for execution of the query, can modify the result of SQL Server from an existing execution. The modified cached plan performs better result as compared to the created plan when the query gets executed first.
The SQL Server engine supports adaptive query processing, ability to meet the users need. In other terms, results of the query optimizer can alter later steps within the plan, probably better. Some plans are cached plans, notable the actually granted memory for execution of the query, can modify the result of SQL Server from an existing execution. The modified cached plan performs better result as compared to the created plan when the query gets executed first.
The SQL Server engine supports adaptive query processing, ability to meet the users need. In other terms, results of the query optimizer can alter later steps within the plan, probably better. Some plans are cached plans, notably the actually granted memory for execution of the query, can modify the result of SQL Server from an existing execution. The modified cached plan performs better result as compared to the created plan when the query gets executed first.
Some plans are cached plans, notably the actually granted memory for execution of the query, can modify the result of SQL Server from an existing execution. The modified cached plan performs better result as compared to the created plan when the query gets executed first.
- Faster queries just by upgrading
- Interleaved execution
- Batch mode memory grant feedback
- Batch mode adaptive joins
Supports the Graph Database
The major feature of SQL Server 2017 is a graph data within a core database engine. For this context, the term ‘graph’ is used in the mathematical context, defined as a set of vertices that connect to all the nodes. In latest years, graph data has been collected because of the natural social media.
Graph data from which most of the people are actually connected. Social media has to rely on the graph data and the SQL Server provides a direct support for graph data, a number of new applications will develop rapidly. In the traditional relational database, graph data has the capability to model ‘many-to-many’ relationship.
SQL Server has provided two different kinds of tables, named as NODE and EDGE are exactly as their names imply. A node is marked in CREATE TABLE statement can cause the SQL Server to create a NodeID for each row automatically. The EDGE storage is very simple, which records the direct connection between two nodes. There are multiple node tables as needed to describe all the entities in the data model, and creates an individual edge table for connecting such data entities.
- Store relationships under nodes/edges
- Analyze interconnected data using node/edge query syntax
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:
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: 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.
The Output:
Now, we can use a TRIM function which can remove the spaces from both ends of the string.
TRANSLATE: 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.
The output:
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.It allows you to resume an online index rebuild operation from where it stopped after a failure. You can also Pause and later Resume an Online index rebuild operation.
New dynamic management views (DMVs)
- dm_db_log_stats – This view exposes summary level attributes and information on transaction log files.
- dm_tran_version_store_space_usage – This view tracks version store usage per database.
- dm_db_log_info – This view exposes VLF information to monitor, alert, and avert potential transaction log issues.
- dm_db_stats_histogram – This is a new dynamic management view for examining statistics.
- dm_os_host_info – This view provides operating system information for both Windows and Linux.
Clusterless read scale with availability groups
Unlimited, geo-distributed, Linear read scaling
Enhanced performance for natively compiled T-SQL modules
- OPENJSON, FOR JSON, JSON
- CROSS APPLY operations
- Computed Columns
Download SQL Server 2017:
To download the SQL Server 2017 you can Register and Download the Full version or Free evaluation version (180 days).
Or, directly download the ISO: SQLServerVnextCTP2.0-x64-ENU.iso
Reference :
- SQL Server 2017 official Page
- MSDN Blog announcement
- Docs for SQL Server 2017
- First release candidate of SQL Server 2017
Please feel free to comment your opinion about this article or whatever you feel like telling me. Also if you like this article, don’t forget to share this article with your friends. Thanks!
Happy Coding !!
Techie Thoughts 