What's New in SQL Server 2017: A Comprehensive Overview of Key Features and Enhancements

Applies to: Sql Server 2017 (14.x) and later

SQL Server 2017 marks a significant advancement, transforming SQL Server into a versatile platform that offers users a wide array of choices. This version empowers developers with flexibility in development languages, diverse data types, and deployment options across on-premises environments, cloud solutions, and various operating systems, notably extending its capabilities to Linux, Linux-based containers, and Windows. This article delivers a concise summary of the innovative features introduced in SQL Server 2017 across key functional domains, along with direct links for those seeking in-depth exploration. For readers particularly interested in leveraging SQL Server within Linux environments, we recommend consulting SQL Server on Linux.

Download SQL Server 2017 Release – October 2017

Note

It’s important to note that in addition to the groundbreaking features outlined below, Microsoft has consistently released cumulative updates following the General Availability (GA) release of SQL Server 2017. These updates are crucial, delivering numerous enhancements and critical fixes. For the most current information on the latest Cumulative Update (CU) release, please refer to SQL Server 2017 Cumulative updates.

SQL Server 2017 Database Engine Innovations

SQL Server 2017’s Database Engine is packed with a host of novel features, significant enhancements, and under-the-hood performance boosts, designed to optimize database operations and application performance.

Enhanced CLR Assembly Management

Addressing security concerns introduced in earlier Community Technology Previews (CTP), SQL Server 2017 introduces a refined approach to managing CLR assemblies. Administrators can now curate a list of trusted assemblies, providing a practical workaround for the clr strict security feature initially outlined in CTP 2.0. To facilitate this enhanced control, new system stored procedures – sp_add_trusted_assembly and sp_drop_trusted_assembly – and the catalog view sys.trusted_assemblies have been implemented. This empowers database professionals with granular control over trusted CLR components.

Resumable Online Index Rebuilds

Index management in SQL Server 2017 takes a leap forward with the introduction of resumable online index rebuilds. This feature revolutionizes index maintenance by allowing online index rebuild operations to gracefully resume from the point of interruption. Whether due to a failover event or insufficient disk space, the rebuild process can pick up exactly where it left off, minimizing downtime and administrative overhead. Furthermore, administrators gain the flexibility to pause and manually resume online index rebuilds, providing unprecedented control over index operations. For detailed usage and best practices, consult ALTER INDEX and Guidelines for online index operations.

Mitigation of Identity Column Gaps

SQL Server 2017 addresses potential data integrity issues related to identity columns with the new IDENTITY_CACHE option within ALTER DATABASE SCOPED CONFIGURATION. This setting is specifically designed to prevent gaps in identity column values that can arise from unexpected server restarts or failovers to secondary servers. By leveraging identity caching, SQL Server 2017 ensures the continuity and predictability of identity values, critical for applications relying on sequential identifiers. More details are available in ALTER DATABASE SCOPED CONFIGURATION.

Adaptive Query Processing Suite

Query processing in SQL Server 2017 reaches a new level of intelligence with adaptive query processing. This suite of enhancements allows the query optimizer to dynamically adjust optimization strategies based on real-time runtime conditions of the application workload. The initial release of this feature family includes three groundbreaking improvements:

Batch Mode Adaptive Joins: Dynamically selects the most efficient join type (hash join or nested loop join) at runtime based on data volume, optimizing query performance.
Batch Mode Memory Grant Feedback: Intelligently adjusts memory grants for batch mode operators based on past execution history, preventing excessive memory allocation or memory spills.
Interleaved Execution for Multi-Statement Table Valued Functions: Enables the query optimizer to use cardinality estimates from the actual first execution of a multi-statement table-valued function (MSTVF) to create a more optimal query plan, particularly beneficial for queries involving MSTVFs.

Explore these features in depth at Intelligent query processing in SQL databases.

Automatic Database Tuning

SQL Server 2017 introduces automatic database tuning, a proactive approach to database performance management. This intelligent feature continuously monitors database performance, identifies potential query performance bottlenecks, and provides actionable recommendations for resolution. Going a step further, automatic tuning can be configured to automatically implement recommended fixes, significantly reducing manual tuning efforts and ensuring consistent optimal performance. Learn more about this capability at Automatic tuning.

Graph Database Capabilities

SQL Server 2017 expands its data modeling capabilities to natively support graph databases. This integration allows for the efficient modeling of complex many-to-many relationships. Key features include:

New CREATE TABLE syntax extensions for defining node and edge tables, the fundamental building blocks of graph databases.
The MATCH keyword, a powerful construct for querying graph relationships using pattern matching, simplifying traversal and analysis of interconnected data.

Delve into the world of graph processing with SQL Server 2017 at Graph Processing with SQL Server 2017.

Enhanced Security with CLR Strict Security

Security is paramount, and SQL Server 2017 strengthens its security posture with the clr strict security sp_configure option. Enabled by default, this feature enforces stricter security policies for CLR assemblies, mitigating potential risks associated with untrusted code execution within the database engine. For a comprehensive understanding of this security enhancement, refer to CLR strict security.

Expanded TempDB File Initialization Size

Setup enhancements in SQL Server 2017 address the demands of high-performance workloads by allowing administrators to specify a larger initial size for tempdb files during installation. The permissible initial size now extends up to 256 GB (262,144 MB) per file. Furthermore, the setup process includes a warning mechanism if the file size is set above 1GB without enabling Instant File Initialization (IFI), guiding users towards optimal configuration practices.

Intelligent Differential Backup Tracking

Backup strategies become more efficient with the introduction of the modified_extent_page_count column in the sys.dm_db_file_space_usage dynamic management view. This valuable column provides granular tracking of differential changes within each database file. This detailed change tracking empowers sophisticated backup solutions to intelligently perform differential or full backups based on the actual percentage of changed pages within the database, optimizing backup times and storage utilization.

Filegroup Specific SELECT INTO Operations

The SELECT INTO T-SQL syntax gains enhanced flexibility in SQL Server 2017. It now supports loading newly created tables into a specific FileGroup, overriding the user’s default filegroup setting. This is achieved through the introduction of the ON keyword within the SELECT INTO statement, offering precise control over data placement.

Cross-Database Transactions in Always On Availability Groups

Transaction management in Always On Availability Groups is streamlined in SQL Server 2017 with the introduction of cross-database transaction support. This enhancement extends transactional consistency across all databases participating in an Always On Availability Group, even those residing within the same instance. This capability simplifies complex application architectures that span multiple databases. Detailed information is available at Transactions – Always On Availability Groups and Database Mirroring.

Availability Groups Enhancements

SQL Server 2017 introduces several key improvements to Availability Groups, enhancing flexibility and deployment options:

Read-Scale Support Without Cluster: Enables read-scale deployments without the necessity of a Windows Server Failover Cluster (WSFC), simplifying configurations for read-heavy workloads.
Minimum Replica Commit Availability Groups Setting: Introduces finer-grained control over data loss tolerance and availability by allowing configuration of the minimum number of synchronous replicas required for transaction commit.
Windows-Linux Cross-OS Migrations and Testing: Facilitates cross-platform migrations and testing scenarios between Windows-based and Linux-based SQL Server deployments within Availability Groups, fostering hybrid cloud strategies.

New Dynamic Management Views (DMVs)

SQL Server 2017 expands its diagnostic and monitoring capabilities with several new Dynamic Management Views (DMVs), providing deeper insights into system performance and operational metrics. (List of new DMVs would be listed here in a complete article, as per original article structure).

Database Tuning Advisor Improvements

The Database Tuning Advisor (DTA), a crucial tool for performance optimization, receives enhancements in SQL Server 2017. These improvements encompass additional tuning options and overall performance optimizations within the DTA engine itself, leading to more effective and efficient tuning recommendations.

In-Memory Database Enhancements

In-memory OLTP capabilities are further refined in SQL Server 2017:

Computed Columns in Memory-Optimized Tables: Computed columns are now fully supported within memory-optimized tables, expanding the design possibilities for in-memory database schemas.
Full JSON Function Support in Natively Compiled Modules: Natively compiled stored procedures and functions gain full support for JSON functions, enabling high-performance JSON processing within in-memory OLTP workloads.
CROSS APPLY Operator in Natively Compiled Modules: The CROSS APPLY operator is now supported in natively compiled modules, increasing the expressiveness and flexibility of T-SQL logic within in-memory contexts.

New String Functions

String manipulation in T-SQL becomes more powerful with the addition of new string functions in SQL Server 2017:

CONCAT_WS: Concatenates two or more string values, but adds a separator between each value.
TRANSLATE: Returns the string provided as first argument after some characters specified in the second argument are translated into a destination set of characters specified in the third argument.
TRIM: Removes leading and trailing spaces, or specified characters, from a string.
WITHIN GROUP for STRING_AGG: The STRING_AGG function now supports the WITHIN GROUP clause, enabling ordered concatenation of strings within a group.

Enhanced Bulk Access Options

Bulk data operations are streamlined with new bulk access options (BULK INSERT and OPENROWSET(BULK…)) tailored for CSV and Azure Blob files, simplifying data ingestion from common data sources.

Memory-Optimized Object Improvements

Further enhancements to memory-optimized objects include:

sp_spaceused Support: The sp_spaceused stored procedure now provides space usage information for memory-optimized tables.
Relaxed Index Limitation: The previous limitation of 8 indexes per memory-optimized table has been removed, offering greater flexibility in index design.
sp_rename Support: The sp_rename stored procedure can now be used to rename memory-optimized tables and natively compiled T-SQL modules.
CASE and TOP (N) WITH TIES in Natively Compiled Modules: Natively compiled T-SQL modules now support CASE expressions and the TOP (N) WITH TIES clause, enhancing their analytical capabilities.
Azure Storage for Memory-Optimized Filegroups: Memory-optimized filegroup files can now be stored, backed up, and restored directly on Azure Storage, facilitating cloud-integrated in-memory database solutions.

DATABASE SCOPED CREDENTIAL Securable Class

Security management is enriched with the introduction of DATABASE SCOPED CREDENTIAL as a new class of securable. This allows for granular permission control at the database scope, supporting permissions such as CONTROL, ALTER, REFERENCES, TAKE OWNERSHIP, and VIEW DEFINITION. Additionally, ADMINISTER DATABASE BULK OPERATIONS is now visible within sys.fn_builtin_permissions, improving permission visibility.

Compatibility Level 140

SQL Server 2017 introduces Database COMPATIBILITY_LEVEL 140, setting the stage for leveraging the latest query optimizer improvements and database engine behaviors. Setting the compatibility level to 140 unlocks the full potential of SQL Server 2017 features.

SQL Server 2017 Integration Services (SSIS) Advancements

SQL Server 2017 Integration Services (SSIS) introduces significant enhancements, particularly in scale-out capabilities and platform support.

Scale Out Feature Enhancements

The new Scale Out feature in SSIS undergoes several key improvements and changes, designed to enhance robustness and usability. For a comprehensive overview, refer to What’s New in Integration Services in SQL Server 2017.

Scale Out Master High Availability: Scale Out Master now supports high availability configurations, ensuring resilience and continuity for scaled-out SSIS deployments.
Improved Failover Handling for Execution Logs: The failover mechanisms for execution logs originating from Scale Out Workers are enhanced, improving the reliability of execution logging in scale-out environments.
Parameter Renaming for Consistency: The parameter runincluster of the stored procedure [catalog].[create_execution] is renamed to runinscaleout for improved consistency and readability, aligning parameter naming with the scale-out feature terminology.
Default Execution Mode Global Property: The SSIS Catalog now includes a new global property to define the default execution mode for SSIS packages, simplifying configuration management for package execution behavior.

Use32BitRuntime Parameter in Scale Out

Within the Scale Out for SSIS feature, users now have the flexibility to utilize the Use32BitRuntime parameter when initiating package executions. This option provides compatibility and control over the runtime environment for SSIS package execution within scale-out scenarios.

SSIS Support for Linux

A groundbreaking addition in SQL Server 2017 SSIS is support for SQL Server on Linux. A new package empowers users to execute SSIS packages on Linux platforms directly from the command line, expanding the deployment landscape for SSIS workloads. Further details are available in the blog post announcing SSIS support for Linux.

Simplified Scale Out Management

The Scale Out for SSIS feature dramatically simplifies the process of running SSIS packages across multiple machines. This capability streamlines the management and execution of SSIS workloads in distributed environments. Explore the details of SSIS Scale Out at Integration Services Scale Out.

Enhanced OData Connectivity

OData Source and OData Connection Manager in SSIS gain expanded connectivity, now supporting connections to OData feeds from Microsoft Dynamics AX Online and Microsoft Dynamics CRM Online. This enhancement simplifies data integration with Dynamics 365 cloud services.

For a comprehensive understanding of all SSIS enhancements in SQL Server 2017, consult What’s New in Integration Services in SQL Server 2017.

SQL Server 2017 Master Data Services (MDS) Improvements

SQL Server 2017 Master Data Services (MDS) focuses on enhancing performance and user experience, particularly in upgrade scenarios and data management operations.

Upgrade Experience and Performance Enhancements

Upgrading to SQL Server 2017 Master Data Services from previous versions (SQL Server 2012, SQL Server 2014, and SQL Server 2016) is significantly improved in terms of both experience and performance. The upgrade process is now more streamlined and efficient, minimizing downtime and disruption.

Explorer Page Sorted Lists

User navigation within the MDS Web application is enhanced with sorted lists in the Explorer page. Entities, collections, and hierarchies are now presented in sorted order, improving discoverability and ease of navigation within complex MDS models.

Staging Performance Optimization

Performance optimizations are implemented for staging large volumes of records (millions) using the staging stored procedure in MDS. This improvement accelerates data loading and processing for large-scale master data management scenarios.

Manage Groups Page Performance

Expanding the Entities folder on the Manage Groups page within the Security section of the Web application now exhibits improved performance. This enhancement addresses performance bottlenecks when assigning model permissions, particularly in MDS models with a large number of entities. For further details on this performance improvement, refer to https://support.microsoft.com/help/4023865?preview. Guidance on assigning permissions is available at Assign Model Object Permissions (Master Data Services).

SQL Server 2017 Analysis Services (SSAS) Innovations

SQL Server 2017 Analysis Services (SSAS) introduces a wealth of enhancements, primarily focused on tabular models, bringing new features and capabilities to modern BI solutions.

Tabular Mode as Default Installation

A significant shift in SQL Server 2017 SSAS is the adoption of Tabular mode as the default installation option. This reflects the growing prominence of tabular models for modern analytical workloads and simplifies the initial setup experience for SSAS.

Object-Level Security for Tabular Models

Security in tabular models is enhanced with the introduction of object-level security. This feature allows administrators to secure the metadata of tabular models at a granular level, controlling access to specific tables, columns, or measures. Object-level security enhances data governance and compliance within SSAS tabular deployments.

Date Relationships for Simplified Modeling

Relationship creation in tabular models is simplified with the introduction of date relationships. SSAS 2017 can now automatically detect and create relationships based on date fields, streamlining the modeling process for time-series data and date-driven analysis.

Expanded Data Source Support with Get Data (Power Query)

Data connectivity in SSAS tabular models is significantly expanded with new Get Data (Power Query) data sources. Furthermore, existing DirectQuery data sources now support M queries, providing a richer and more versatile data acquisition experience.

DAX Editor in SSDT

Developers benefit from an integrated DAX Editor within SQL Server Data Tools (SSDT). This dedicated editor provides enhanced syntax highlighting, IntelliSense, and debugging capabilities for DAX (Data Analysis Expressions) code, improving developer productivity when working with tabular models.

Encoding Hints for Large Tabular Models

For optimizing data refresh operations in large in-memory tabular models, SQL Server 2017 SSAS introduces encoding hints. This advanced feature provides fine-grained control over data encoding strategies, potentially reducing memory footprint and accelerating data refresh times for very large tabular models.

1400 Compatibility Level for Tabular Models

SSAS 2017 introduces support for the 1400 Compatibility level for tabular models. To leverage the latest features and enhancements in tabular modeling, new tabular model projects should be created at the 1400 compatibility level, and existing projects can be upgraded. To create or upgrade to compatibility level 1400, download and install SQL Server Data Tools (SSDT) 17.0 RC2.

Modern Get Data Experience for Tabular Models

Tabular models at the 1400 compatibility level benefit from a modern Get Data experience, aligning with the Power Query data acquisition interface found in Power BI and Excel. This unified experience simplifies data connectivity and transformation for tabular model development. Explore this modern experience in the Analysis Services Team Blog.

Hide Members Property for Ragged Hierarchies

Visualizations of ragged hierarchies in tabular models are enhanced with the Hide Members property. This property allows hiding blank members within ragged hierarchies, improving the clarity and visual appeal of hierarchical data representations.

Detail Rows End-User Action

End-user interactivity with tabular model reports is enhanced with the new Detail Rows end-user action, exposed as Show Details. This feature allows users to drill down into aggregated data and view the underlying detail rows contributing to an aggregated value. New DAX functions, SELECTCOLUMNS and DETAILROWS, are introduced for creating Detail Rows expressions, enabling developers to customize the detail data exposed to end users.

DAX IN Operator

DAX query capabilities are extended with the introduction of the IN operator. This operator simplifies DAX expressions by allowing the specification of multiple values within a filter condition, making DAX code more concise and readable.

For a comprehensive exploration of all SSAS innovations in SQL Server 2017, consult What’s new in SQL Server Analysis Services.

SQL Server 2017 Reporting Services (SSRS) Evolution

SQL Server Reporting Services (SSRS) in SQL Server 2017 undergoes a distributional change, and introduces new features focused on collaboration and modern application development.

Separate Installation from SQL Server Setup

SQL Server Reporting Services is no longer integrated into the main SQL Server setup process. To install SSRS 2017, users must now download it separately from the Microsoft Download Center: download Microsoft SQL Server 2017 Reporting Services. This separation allows for independent release cycles and updates for SSRS.

Report Comments and Collaboration

Collaboration around reports is enhanced with the introduction of report comments. Users can now add comments directly to reports, facilitating discussions, providing context, and collaborating with other users directly within the reporting platform. Attachments can also be included with comments, enriching collaborative communication.

Native DAX Query Support in Report Builder and SSDT

Report creation is streamlined with native DAX query support in the latest releases of Report Builder and SQL Server Data Tools. Users can now construct native DAX queries against supported SQL Server Analysis Services tabular data models directly within the query designers by dragging and dropping fields, simplifying report development against tabular data sources. Learn more in the Reporting Services blog.

RESTful API for Modern Application Development

To facilitate the development of modern applications and enable customization, SSRS 2017 now supports a fully OpenAPI compliant RESTful API. This API enables programmatic interaction with SSRS functionalities, opening up possibilities for embedding and integrating SSRS into custom applications and workflows. The complete API specification and documentation are available on swaggerhub.

For a detailed overview of new features in SSRS 2017, refer to What’s new in SQL Server Reporting Services (SSRS).

Machine Learning Revolution in SQL Server 2017

SQL Server 2017 marks a significant expansion in its advanced analytics capabilities, rebranding SQL Server R Services as SQL Server Machine Learning Services to reflect the inclusion of Python support alongside the R language.

Python and R Language Support

SQL Server Machine Learning Services offers the choice of using either R or Python for in-database analytics. Users can leverage Machine Learning Services (In-Database) to execute R or Python scripts directly within SQL Server, or opt for Microsoft Machine Learning Server (Standalone) for deploying and consuming R and Python models independently of SQL Server.

Python ML and AI Libraries

SQL Server developers gain access to the extensive Python machine learning and AI ecosystem, including popular open-source libraries and cutting-edge innovations from Microsoft:

revoscalepy: The Python counterpart to RevoScaleR, offering parallel algorithms for linear and logistic regressions, decision trees, boosted trees, and random forests. It also provides a comprehensive set of APIs for data transformation, data movement, remote compute contexts, and data sources.
microsoftml: A state-of-the-art Python package featuring advanced machine learning algorithms and transforms, including deep neural networks, fast decision trees and decision forests, and optimized algorithms for linear and logistic regressions. Pre-trained models based on ResNet architectures are also included for image feature extraction and sentiment analysis tasks.

Streamlined Python Operationalization with T-SQL

Deploying Python code within SQL Server becomes seamless with the sp_execute_external_script stored procedure. This enables easy operationalization of Python scripts within the database. Performance is optimized through data streaming from SQL to Python processes and MPI ring parallelization.

Python in SQL Server Compute Contexts

Data scientists and developers can remotely execute Python code from their preferred development environments within the SQL Server compute context. This allows for data exploration and model development without the need to move data out of the database, enhancing efficiency and security.

Native Scoring with PREDICT function

SQL Server 2017 introduces native scoring capabilities with the PREDICT function in Transact-SQL. This function enables high-performance model scoring within any SQL Server 2017 instance, even without R or Python components installed. The only prerequisite is that the model must be trained using supported RevoScaleR or revoscalepy algorithms and saved in a new compact binary format.

Package Management for R Packages

Enhanced package management for R is introduced through the CREATE EXTERNAL LIBRARY T-SQL statement. This empowers DBAs with greater control over R packages within SQL Server. Roles can be used to manage private or shared package access, and R packages can be stored directly within the database for centralized management and sharing.

Performance Improvements for sp_execute_external_script

The sp_execute_external_script stored procedure undergoes performance optimizations, including support for batch mode execution for columnstore data, further accelerating in-database analytics workloads.

For a deeper dive into the machine learning advancements in SQL Server 2017, consult What’s new in SQL Server Machine Learning Services.