SQL Server 2017 was released in October 2017. Many users were still getting accustomed to all the features in SQL 2016, but Microsoft quickly brought in another set of features that are sure to please. Microsoft representatives and user feedback confirm that SQL Server 2017 represents a significant step in development compared to, for example, SQL Server 2016 or previous versions.

One of the major changes is that it finally gives you the option to choose the platform on which SQL Server will run – whether it’s Windows or Linux. So, what else can it do?

SQL Server 2017 Runs on Windows, Linux, and Docker Containers

Microsoft has expanded the number of operating systems that SQL Server supports. Version 2017 can now be installed on:

• Windows
• Red Hat Enterprise Linux 7.3
• SUSE Enterprise Linux Server v12 SP2
• Ubuntu 16.04:LTS
• Docker Engine 1.8+

Furthermore, the solution for Linux does not have limited functionality. It offers multi-layered security, encryption of stored and transmitted data, and other features known from the Windows version.

Graphical Database Visualization – Data Modeling

Data modeling has always been a weak point of SQL Server in previous editions – see the article SQL Server ER Diagrams – Don’t Expect Miracles. If you are dealing with complex database relationships, you will appreciate the new feature that allows you to model data from the database into a graph format.

You can clearly see entities (as nodes) and relationships between them (edges between nodes) in a single image. Microsoft has introduced enhanced syntax for SQL Graph. For example, you can model a node by adding the keyword “AS NODE” when creating a table:

        CREATE TABLE Parent (ID INTEGER PRIMARY KEY, name VARCHAR(100)) AS NODE;

Edges are created similarly:

        CREATE TABLE livesIn AS EDGE;

Python Support is Welcome

SQL Server R Services have been renamed to SQL Server Machine Learning Services, reflecting support not only for the R language but also for Python. In version 2016, support for R processing was already available, but Python is a novelty. Machine Learning Services can be used to run Python scripts on SQL Server, allowing data to be processed directly on the server without the need for data movement.

Resumable Online Index Rebuilds

SQL Server 2017 allows you to pause an index rebuild operation and later resume it to complete the work. With resumable index rebuilds, you can also restart a failed operation.

New CLR Security

Code Access Security (CAS) in the .NET framework is no longer supported as a security boundary – an assembly marked as SAFE may be able to run unsafe code or access external resources. Therefore, SQL Server 2017 implements CLR Strict Security. If you enable this option, all SAFE assemblies and EXTERNAL_ACCESS will be considered safe.

Identity Cache Behavior Change

In older versions of SQL Server, the identity cache would be cleared if the server did not shut down correctly, resulting in missing information. Microsoft has introduced a new configuration to address this issue.

Simplified Coding with New T-SQL Features

T-SQL brings several new functions for working with strings – for example, TRIM, CONCAT_WS, TRANSLATE, and STRING_AGG. For comparison, in older versions of SQL Server, such as 2016 and earlier, you had to use LTRIM and RTRIM to remove spaces from the beginning and end of a string or variable. SQL Server 2017 can handle this with a single call to the TRIM function.

New Scale Out Features

SSIS enhances Scale Out features, which support high availability and bring improvements in log processing. With Scale Out, you can distribute package execution across multiple machines, improving overall performance through parallel execution. However, you need to configure this feature. You install one Scale Out Master service and then several Scale Out Worker services. The Scale Out Master manages communication with Scale Out Workers and coordinates parallel SSIS package execution.

Automatic Database Tuning

Automatic tuning alerts you whenever it detects potential performance issues. It either advises you on what to do or fixes the problem on its own.

Adaptive Query Processing

SQL Server 2017 comes with enhanced query processing. In essence, it first decides which method of executing a specific query will be the best. There are a total of three modes to select the best way to execute. One, for example, focuses on minimal memory usage.