Newly Introduced Additional Features of SQL Server 2017

Newly Introduced Additional Features of SQL Server 2017

SQL Server 2017

SQL Server 2017 is launched with too many features. One of the most awaited and advantageous features of this version of SQL Server are its Linux system support. Yes, now this new SQL Server version also supports Linux operating system.

This article covers new additional features of SQL Server and one most interesting query that how can you identify through query about when the server was last started or recent start time of SQL Server through a single line query. Today SQL Server is a popular and most used system that can resolve your many queries and increase the efficiency of database operations.

Newly Introduced Features of SQL Server 2017

Many new features have been added to the SQL database engine and there is present a few of them that can help you in deciding that whether it has become your Server or not?

SQL Server for Linux

Now SQL Server has not been a database for Windows-based machines only but it can be used for different Linux flavors too. Applications can be developed with SQL Server on Ubuntu or Docker like Linux and can be also deployed on these platforms. It can now compete with another popular RDBMS like Oracle. As Oracle is by far the most used RDBMS for Linux based machines and applications. Now SQL Server can be easily installed on Linux machines and many articles are there that discuss SQL Server installation on Linux machine.

Adaptive Query Processing

A new generation of query processing has been introduced in SQL Server 2017 edition and it is adaptive query processing. This query processing technique will adapt optimization strategy to the workloads runtime conditions of your application. Cardinality estimation process is basically responsible to count the number of rows processed during query execution that is required information to build or prepare the estimation plan.

Wrong or inappropriate estimation can be a reason to take a poor decision by query optimizer and as a result of that it can select wrong algorithm and order of execution can also be affected. Adaptive query processing is one of the most awaited features in SQL Server. As in earlier versions of SQL Server if the poor assumption was made about cardinality then it worsens the query processing plan that cannot be modified at a later stage.

Operation of Resume Online Index Rebuild

In SQL Server 2017, the feature of resumable online index rebuild has been introduced. With the help of this feature, online index rebuild operation can be paused and resume. This feature is quite useful especially when any failure occurs in the database. It can restart the database indexing at the same point where it was stopped before failure. Here, it can be understood by taking the example of the case where system resources may be required to be free up for some time to execute any higher priority task or to complete the index rebuild in a separate maintenance window especially when the available maintenance window is too short for a large sized database table.

Graphical Database

To model many-to-many relationship, the user can implement graphical database capability in SQL Server 2017. This graphical relationship can be integrated into Transact-SQL and SQL Server can help as a foundational database management system for the user.

Here, the graph is a collection of vertices and edges in which vertices can represent any entity while the edges are used to represent the relationship between these entities or two nodes. Moreover, these nodes and edges can further have some properties associated with them.

Availability of Groups without Clusters

Through SQL Server 2017 AOAG or Always on Availability Group feature can be easily configured without windows clusters. To configure AOAG between Linux and Windows-based SQL Server, this feature was added to SQL Server 2017. This additional feature of SQL Server has removed the requirement of windows cluster to configure AOAG between Window and Linux-based machines or systems.

Addition of new functions

Few new additional T-SQL functions have been added to SQL Server 2017. These functions will provide query processing more effective and powerful. These functions are much important for SQL developers. Following are the newly introduced functions:

  • CONTACT_WS
  • TRANSLATE
  • TRIM
  • STRING_AGG

Cross-OS AOAG

Cross-OS migrations can be tested through Availability Groups that help in Windows-Linux based systems. Now SQL Server 2017 can be configured between windows and Linux based SQL Server. The option of clusters AOAG has been given by the Microsoft.

New DMVs

Many new DMVs have been introduced in SQL Server 2017. Below is the list of these DMVs you can check their one-line descriptions listed in front of these DMVs:

  • dm_db_stats_histogram: This DMV is used to examine added statistics
  • dm_tran_version_store_space_usage: It is used to track per database store usage version
  • Sys .dm_os_host_info: It is added to provide the Windows and Linux based operating system information.
  • Sys .dm_os_sys_info: Three new columns have been added that can provide the system information that is numa_node_count, socket_count, cores_per_socket
  • Sys .dm_db_file_space_usage: This DMV is introduced to track the differential changes in each database file.

New DMFs

Few new DMFs are also introduced in the SQL Server 2017. Following is the list of these DMFs, you can check their information written in front of them:

  • Sys .dm_db_log_info: This DMF is introduced to expose VLF information that is similar to DBCC LOGINFO that is used to alert, monitor and avert potential transaction logs issues that can be caused due to the number of VLFs.
  • Sys .dm_db_log_stats: This is another DMF that is used to expose summary level attributes of transaction log files. It can help in transaction log health monitoring.

Command to tell when SQL Server Service was last started?

If you want to know that how can you determine programmatically when SQL Server was last started then you can do this with the help of a command or a mini-program or script. Following are two ways to do this:

SELECT login_time FROM sys.dm_exec_Sessions WHERE session_id=1;

OR

SELECT create AS Datetime_Instance_Started FROM sysdatabases WHERE name=’tempdb’

In 2005 version of SQL Server, you can use DMV to know the exact date and time of the last login.

Final Words

Here we have explained SQL Server 2017 new features that are much more useful for the SQL Server developers. SQL has launched a number of versions but in this version of SQL Server, a new feature that is a support for Linux based machines is introduced. As today many applications are developed for LINUX OS, so this feature will help the developers to develop an application successfully without any failure.

Post to Twitter Post to Facebook

About manchun

Manchun Pandit loves pursuing excellence through writing and have a passion for technology. he has successfully managed and run personal technology magazines and websites. he currently writes for JanBaskTraining.com, a global training company that provides e-learning and professional certification training.