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.