New points in SQL Server 2017

SQL Server 2017 is officially released in October 2017. The first parts of SQL Server 2017 have been released since the end of 2016, as of the end of 2017 it has 10 releases.

The SQL Server 2017 version is primarily connected to Linux, bringing the power of SQL to Linux. In short, you can install SQL Server 2017 on Linux, using SQL Server 2017 on Linux-based docker containers. SQL Server 2017 also allows you to choose development languages, develop it on-premise or cloud-based.

In this version, SQL Server 2017 also improves performance, scalability and features in each part such as Database Engine, Integration Services, Master Data Services, Analysis Services, etc. In this article we will look at through part by part.

New feature in Database Engine

identity_cache

New points in SQL Server 2017 Picture 1

This option helps you avoid deviations in the value of the ID column, in case the server shuts down suddenly or performs a failover, or even switches to a secondary server. It is used with the ALTER DATABASE SCOPED CONFIGURATION command, to enable database configuration settings. The syntax is as follows:

 ALTER DATABASE SCOPED CONFIGURATION { { [ FOR SECONDARY] SET } } | CLEAR PROCEDURE_CACHE | SET < set_options > [;] < set_options > ::= { MAXDOP = { | PRIMARY } | LEGACY_CARDINALITY_ESTIMATION = { ON | OFF | PRIMARY } | PARAMETER_SNIFFING = { ON | OFF | PRIMARY } | QUERY_OPTIMIZER_HOTFIXES = { ON | OFF | PRIMARY } | IDENTITY_CACHE = { ON | OFF } }

Improved adaptive query processing

If you want to improve query performance, this new feature will help significantly. It is supported in SQL Server and Azure SQL Database.

This is the usual optimization process when executing SQL queries:

1. First, the query optimizer will compute all possible query execution plans for the query just created.
2. Next, it will show the best / fastest plan.
3. Finally, the estimated best plan will be chosen to execute the query and the execution process will then begin.

The above process has the following disadvantages:

1. If the estimate of the wrong plan is the best, it will affect performance.
2. If insufficient memory is allocated for the execution of the optimal plan, a memory overflow error will occur.

Here are the features that SQL Server 2017 has included to overcome these disadvantages:

Batch Mode Memory Grant Feedback (Feedback on memory allocation for batch mode): This feedback will recalculate the memory required for executing the plan and allocate memory for it from the cache.

Batch Mode Adaptive Joins (Join adapting to batch mode): There are 2 types of join: Hash and nested loop. When the execution plan entry is entered and scanned for the first time, it determines which join type to apply to get the output at the optimum speed.

Interleaved Execution: During the execution of the optimal plan, this feature will "pause" when encountering multi-stament table valued functions to only calculate the perfect elements. elements in this table, and then continue to optimize.

Automatic Tuning

This feature examines problems in query performance, identifies them and fixes with proposed solutions. These are the automactic tuning techniques available in this feature:

Automatic Correction (Plan): This technique is available in SQL 2017 Database, it will find the performance problems in the query plan given, then fix them with the proposed solutions.

Automatic Management (Index): This technique is included in the SQL 2017 Azure DB, it will identify and correct the order of indexes by deleting the non-standard index and adding the correct index.

New feature in Graph DB

What is Graph DB?

Basically, Graph DB is a set of nodes and edges, the edge represents the relationship between nodes, nodes are entities, an edge can be connected to many nodes. The Graph DB works like a relational database, and you can use it in the following cases:

1. When there is a database in a hierarchical format and want to save multiple parents for a node.
2. When you need to check and analyze link and data relationships.
3. When there are many relationships (relationship).

Here, the MATCH keyword is used to query the Graph table and sort the data, with the help of a single query, the user can query on the graph and relational data.

Always Available (cross-data access)

With the help of this feature, it is now possible to cross-exchange databases between different SQL instances (a SQL instance that can connect to other instance instances).It also supports the exchange of distributed databases.[SQL 2016 also supports cross-database access but only between instances within the same SQL Server.]

Improvement on DTA

In SQL 2017, there were improvements in performance in Database tuning advisor (DTA).Specifically, the options for DTA have been added.

If you don't know what DTA is, then:

DTA is a database tool that performs query processing (processed) and then offers ways to help you improve performance, possibly by changing the database structure. data (eg index, lock).DTA can be used in the following two ways:

1. Using GUI (interface)
2. Use the command utility

New string function

SQL 2017 gives users some new string functions like TRANSLATE, CONCAT_WS, STRING_AGG, TRIM . Let's explore each function one by one.

TRANSLATE

Basically this function takes a character string as input data and then converts these characters to some new characters, see the syntax below:

 TRANSLATE (inputString, characters, translations) 

In the syntax above the length of 'characters' must be the same as in 'translations', otherwise the function will return an error value.For example:

 TRANSLATE ( '6 * {10 + 10} / [6-4]' , '[] {}' , '() ()' ) 

The result returned by the above example will be 6 * (10 + 10) / (6-4).We can see that curly braces and brackets are converted into round brackets.

This function has the same mechanism as the REPLACE function, but the simpler usage is to replace the REPLACE function.For example, if we want to return the result as above but use the REPLACE function, then we have to write the following function, it will not be easy to see at first glance:

 SELECT REPLACE (REPLACE (REPLACE (REPLACE ( '6 * {10 + 10} / [6-4]' , '{' , '(' ), '}' , ')' ), '[' , '(' ), ']' , ')' ); 

CONCATE_WS

The function of this function is simply to concatenate all input arguments with the specified delimiter.Please refer to the syntax below:

 CONCAT_WS (separator, argument1, argument1 [, argumentN] .) 

This function creates single strings by concatenating all arguments together with the help of separators, so it needs at least 2 arguments to produce the output, otherwise the result returns about will error.For example:

SELECTCONCAT_WS (',','Count numers','one','two','three','four')AScounter;

The result returned by the above command will be:one, two, three, four

You can also use database column names instead of encrypted strings.

TRIM

Finally, this function has also appeared in SQL 2017. It basically works like the trim function of C #, ie removing all extra spaces at the beginning and end of the string.The syntax is as follows:

 SELECT TRIM ( 'trim me' ) AS result; 

The result returned by the above command will be:trim me

This function will not remove spaces in the middle of the string.

STRING_AGG

This function concatenates the values ​​of the string with the help of separators while not adding separators at the end of the string.Input data can be VARCHAR, NVARCHAR, you can optionally specify the display order of results using the WITHIN GROUP clause.

See the syntax below:

 STRING_AGG (expression, separator) [] 
 
 :: = 
 WITHIN GROUP ( ORDER BY [ ASC | DESC ]) 

Please refer to the following example:

 SELECT city, 
 STRING_AGG (name, ';' ) WITHIN GROUP ( ORDER BY name ASC ) AS names 
 FROM Students GROUP BY city; 

In the above example, all names have been paired and separated by a semicolon (;).The clause WITHIN GROUP helps us to sort in order.The returned result will be displayed as follows:

New points in SQL Server 2017 Picture 2

What's new in SSRS (Reporting Services) of SQL 2017

1. From now on, setting up SSRS is no longer available on SQL Server settings, you need to download it from the download store [here].
2. From now on, the Query designer will support DAX.Native DAX queries can be created to prevent SSAS (analysis services).This feature will appear on the latest updates of SQL tools and report builders.
3. OpenAPIcommandsare supported by RESTful API, and now RESTful API is supported by SSRS.
4. From now on, you can attach more files to your comments.
5. You can also add comments to the reports.
6. The reporting service portal has been significantly upgraded (this feature is available in SQL 2016).

What's new in SSIS (Integrated Services) in SQL 2017

From now on you can perform SSIS on Linux, increase volume, and extract and convert data directly on Linux.

The scaling feature allows complex integrated systems with many high-performance machines.The scaling feature can perform all operations with the help of Scale Out Master and Scale Out Workers.

What's new in Analysis Services (SSAS) in SQL 2017?

1. The new interface of Get Data is released on SQL 2017 similar to MS Excel, power BI.In addition to data transformation and data mashup features have also appeared, you can do that using the query generator and the M expression.
2. Tabular modefor SSAS - an empire introduced in SQL 2012, has now been upgraded significantly in SQL 2017.
3. SQL 2017 offers new Encoding hints, which are used to optimize table data in large memory.
4. Improve performance for PIVOT.

Machine Learning

We all know that SQL 2016 currently supports R services, and from now on, this service will be renamed to SQL Server Machine learning services.The benefit from this change is that you can easily use the system of R or Python commands on SQL Server.

With this new feature, Python can run in stored procedures.You can even execute commands remotely via SQL Server, which is really useful for Python developers.However, this feature is currently not supported on Linux yet, please wait for the next upgrade.

To use machine learning in a more efficient and optimal way, SQL uses the following solutions:

1. revoscalepy is a new type of library that serves as the foundation for high-performance algorithms, calculations and remote situations. Basically revoscalepy isbased on the RevoScaleR platform (an R service pack).
2. microsoftml is a Microsoft R server cluster that supports machine language algorithms, Microsoft has developed this library for machine learning internally.But over the years, it has improved and nowmicrosoftmlsupports fast data transfer as well as conversion of large documents, etc.

Linux support

Basically, right from the name"SQL 2017 on Linux and Windows" we can know thatthe main purpose of this upgrade is to support the release of products on the Linux platform.Here are some key features of "SQL on Linux":

1. Ability to store core databases
2. Support IPV6
3. NFS support
4. Verify AD on linux
5. Support encryption
6. Can install SSIS on Linux
7. MSSQL-conf command tool is available
8. Seamlessize and liberalize the installation process
9. SQL for Visual studio core (VS core is available on Linux)
10. Cross platform script generator

summary

There will be a lot more to say and learn about SQL Server, we will continue this journey in the next sections.Do not hesitate to ask for comments and questions!

See more:

1. 7 ways to run Linux software on Windows
2. 8 main differences between Windows and Linux
3. Ways to log in database on MS SQL Server
4. How to start and stop services in MS SQL Server