New Features In SQL Server 2017

Introduction

SQL Server 2017 represents major steps towards making SQL Server a platform that gives us choice of:

data types
development languages
hosting environment (on-premises or cloud)
Operating System (Windows, Linux, Linux based docker container)

In this article, I have explained briefly about new features available with SQL Server 2017 (RC2 version).

New / Modified features of Database Engine

New database compatibility level (140) is added
The setup allows specifying initial tempdb size upto 256 GB per file. However, it gives the warning if the size is greater than 1GB, IFI not enabled.
The new graph database capabilities are added for modelling many-to-many relationships. There is new CREATE TABLE syntax for creating node and edge tables, and MATCH keyword for queries.
Enhancement in security of CLR assemblies
Automatic database tuning option provided, it recommends solutions and it can automatically fix identified problems
It provide resumable online index rebuild option to resumes an online index rebuild operation
Loading a table into file group other than the user default file group with "SELECT INTO" T-SQL syntax
Cross database transactions are supported for all the databases which are part of an Always On Availability Group
New dynamic management views are added

sys.dm_db_log_stats: it exposes summary level attributes and information on transaction log files and very helpful for monitoring trnsaction log health
sys.dm_tran_version_store_space_usage: it tracks version store usage per database and it very useful for proctively planning tempdb size
sys.dm_db_log_info: It exposes VLF information to monitor
sys.dm_db_stats_histogram: it exposes statistics
sys.dm_os_host_info: it provide the operating system information (for both windows and linux)

In-memory enhancements

Support for computed column in memory-optimized tables
Support for JSON functions in natively compiled modules
Support CROSS APPLY operator in natively compiled modules

New String functions are added

CONCAT_WS: it concates variables into single string using the first argument as separator
TRANSLATE: it replaces character set in input string with specified character set provided in second argument
TRIM: it remove the space charater or other specified character from beginning and ending of string
WITHIN GROUP is supported for STRING_AGG function

Memory-optimized object enhancements
Removal of the 8 index limitation for memory-optimized tables
The sp_rename supports to memory-optimized tables
CASE and TOP (N) WITH TIES for natively compiled T-SQL modules
Memory-optimized filegroup files can now be stored, backup and restored on Azure Storage
Added supports for bulk access options for CSV and Azure Blob files

New / Modify features of SSRS (Reporting Services)

SSRS is no longer available to install with SQL Server setup.
Comments are available for reports. We can also include the attachments with comments.
We can create native DAX queries for SSAS (SQL Server Analysis Services) tabular data models by dragging and dropping required fields in the query designers
New / Modify features of MDS (Master Data Services)
The Explorer page of the Web application has been updated with sorted lists of entities, collections and hierarchies
The Staging stored procedure help us to improve the performance for staging millions of records
Improve the UI performance like expanding the "Entities" folder on the Manage Groups page to assign model permissions

New / Modified features of SSIS (SQL Server Integration Services)

SSIS also supports SQL Server on Linux
The Scale Out feature has following new / modify features
High availability are supportd in Scale Out Master
The Scale out workers is improved to handle failover
The parameter "runincluster" of the stored procedure "create_execution" is renamed to "runinscaleout"
Catalog has new global property that specify the default mode for executing SSIS packages
It much easier to run SSIS on multiple machines with new Scale Out
OData source and connection manager is supported with Microsoft Dynamics AX Online and Microsoft Dynamics CRM Online

New / Modified features of SSAS (SQL Server Analysis Services)

There are many enhancements for tabular models in SSAS 2017

Support for the 1400 Compatibility level for tabular models
Tabular mode is now the default installation option for SSAS
Object level security added to secure the metadata of tabular models
It is now very easy to create relation based on date fields
The Get Data source (newly added) and DirectQuery data sources (existing feature) support for M queries
DAX Editor introduced for SSDT
In ragged hierarchies, we can hide blank members using "Hide Members" property
The "IN" operator is support in DAX to specifying multiple values
The DETAILROWS functions introduced to Show Details for aggregated information

Summary

There are many new features introduced with SQL server 2017 and also there are some performance improvements in the existing features. Using the following links, we can learn more details about the new features in SQL Server 2017.