Note: SQL Server 2017 also includes the features added in SQL Server 2016 service packs. For those items, see What's New in SQL Server 2016 (Database Engine).
SQL Server Database Engine (CTP 2.1)
- A new, DMF sys.dm_db_log_stats, is introduced to expose summary level attributes and information on transaction log files; useful for monitoring the health of the transaction log.
SQL Server Database Engine (CTP 2.0)
- Resumable online index rebuild. Resumable online index rebuild allows you to resume an online index rebuild operation from where it stopped after a failure (such as a failover to a replica or insufficient disk space). You can also pause and later resume an online index rebuild operation. For example, you might need to temporarily free up systems resources in order to execute a high priority task or complete the index rebuild in another miniatous window if the available maintenance windows is too short for a large table. Finally, resumable online index rebuild does not require significant log space, which allows you to perform log truncation while the resumable rebuild operation is running. See ALTER INDEX and Guidelines for online index operations.
- IDENTITY_CACHE option for ALTER DATABASE SCOPED CONFIGURATION. A new option IDENTITY_CACHE was added to ALTER DATABASE SCOPED CONFIGURATION T-SQL statement. When this option is set to OFF it allows to avoid gaps in the values of identity columns in case a server restarts unexpectedly or fails over to a secondary server. See ALTER DATABASE SCOPED CONFIGURATION.
- CLR uses Code Access Security (CAS) in the .NET Framework, which is no longer supported as a security boundar. A CLR assembly created with
PERMISSION_SET = SAFEmay be able to access external system resources, call unmanaged code, and acquire sysadmin privileges. Beginning with SQL Server 2017, an
clr strict securityis introduced to enhance the security of CLR assemblies.
clr strict securityis enabled by default, and treats
EXTERNAL_ACCESSassemblies as if they were marked
clr strict securityoption can be disabled for backward compatibility, but this is not recommended. Microsoft recommends that all assemblies be signed by a certificate or asymmetric key with a corresponding login that has been granted
UNSAFE ASSEMBLYpermission in the master database. For more information, see CLR strict security.
- SQL Server now offers graph database capabilities to model many-to-many relationships. This includes new CREATE TABLE syntax for creating node and edge tables, and the keyword MATCH for queries. For more information, see Graph Processing with SQL Server 2017.
- Automatic tuning is a database feature that provides insight into potential query performance problems, recommend solutions, and automatically fix identified problems. Automatic tuning in SQL Server, notifies you whenever a potential performance issue is detected, and lets you apply corrective actions, or lets the Database Engine automatically fix performance problems. For more information, see Automatic tuning.
- PERFORMANCE ENHANCEMENT FOR NON CLUSTERED INDEX BUILD ON MEMORY-OPTIMIZED TABLES. Performance of bwtree (non-clustered) index rebuild for MEMORY_OPTIMIZED tables during database recovery has been significantly optimized. This improvement substantially reduces the database recovery time when non-clustered indexes are used.
- sys.dm_os_sys_info has three new columns: socket_count, cores_per_socket, numa_node_count.
- A new column modified_extent_page_count is introduced in sys.dm_db_file_space_usage to track differential changes in each database file of the database. The new column modified_extent_page_count will allow you to build smart backup solution which performs differential backup if percentage changed pages in the database is below a threshold (say 70-80%) else perform full database backup.
- SELECT INTO … ON FileGroup - SELECT INTO now supports loading a table into a filegroup other than a default filegroup of the user using the ON keyword support added in SELECT INTO TSQL syntax.
- Tempdb Setup Improvements - The setup will allow specifying initial tempdb file size up to 256 GB (262,144 MB) per file with a warning to customers if the file size is set to value greater than 1GB and if IFI is not enabled. It is important to understand the implication of not enabling instant file initialization (IFI) where setup time can increase exponentially depending on the initial size of tempdb data file specified. IFI is not applicable to transaction log size so specifying larger value of transaction log can invariably increase the setup time while starting up tempdb during setup irrespective of the IFI setting for SQL Server service account.
- A new dmv sys.dm_tran_version_store_space_usageis introduced to track version store usage per database. This new dmv will be useful in monitoring tempdb for version store usage for you to proactively plan tempdb sizing based on the version store usage requirement per database without any performance toll or overheads of running it on production servers.
- A new DMF sys.dm_db_log_info is introduced to expose the VLF information similar to DBCC LOGINFO to monitor, alert and avert potential transaction log issues caused due to number of VLFs, VLF size or shrinkfile issues experienced by customers.
- Improved Backup performance for small databases on high end servers - While performing backup of databases in SQL Server, the backup process requires multiple iteration of buffer pool to drain the on-going I/Os. As a result, the backup time is not just the function of database size but also a function of active buffer pool size. In SQL Server 2017, the backup is optimized to avoid multiple iterations of buffer pool resulting in dramatic gains in backup performance for small to medium databases. The performance gain reduces as the database size increases as the pages to backup and backup IO takes more time compared to iterating buffer pool.
- DBCC CLONEDATABASE Improvements - DBCC CLONEDATABASE will flush runtime statistics while cloning to avoid missing query store runtime statistics in database clone. In addition to this, DBCC CLONEDATABASE is further enhanced to support and clone fulltext indexes.
SQL Server Database Engine (CTP 1.4)
- There are no new Database Engine features in this CTP.
- This CTP contains bug fixes for the Database Engine.
SQL Server Database Engine (CTP 1.3)
- Indirect checkpoint performance improvements.
- Cluster-less Availability Groups support added.
- Minimum Replica Commit Availability Groups setting added.
- Availability Groups can now work across Windows-Linux to enable cross-OS migrations and testing.
- Temporal Tables Retention Policy support added,
- New DMV SYS.DM_DB_STATS_HISTOGRAM
- Online non-clustered columnstore index buill and rebuild support added
- 5 new dynamic management views to return information about Linux process. For more information, see Linux Process Dynamic Management Views.
- sys.dm_db_stats_histogram (Transact-SQL) is added for examining statistics.
SQL Server Database Engine (CTP 1.2)
- The Database Tuning Advisor (DTA) released with SQL Server Management Studio version 16.4, when analyzing SQL Server 2016 and later, has additional options.
- Improved performance. For more information, see Performance Improvements using Database Engine Tuning Advisor (DTA) recommendations.
-fcoption for allowing recommendations of columnstore indexes. For more information, see DTA Utility and Columnstore index recommendations in Database Engine Tuning Advisor (DTA).
-iqoption for allowing the DTA to review a workload from the Query Store. For more information, see Tuning Database Using Workload from Query Store.
SQL Server Database Engine (CTP 1.1)
- For In-Memory functionality, additional enhancements to memory-optimized tables and natively compiled functions are listed next, and code samples are available in subsequent text:
- Support for computed columns in memory-optimized tables, including indexes on computed columns.
- Full support for JSON functions in natively compiled modules, and in check constraints.
CROSS APPLYoperator in natively compiled modules.
- New string functions CONCAT_WS, TRANSLATE, and TRIM are added.
WITHIN GROUPclause is now supported for the STRING_AGG function.
- Two new Japanese collation families (Japanese_Bushu_Kakusu_140 and Japanese_XJIS_140) were added, and the collation option Variation-selector-sensitive (_VSS) was added for use in Japanese collations. For more detail see Collation and Unicode Support
- New bulk access options (BULK INSERT and OPENROWSET(BULK...) ) enable access data directly from a file specified as CSV format, and from files stored in Azure Blob storage through the new
BLOB_STORAGEoption of EXTERNAL DATA SOURCE.
SQL Server Database Engine (CTP 1.0)
- Database COMPATIBILITY_LEVEL 140 has been added. Customers running in this level will get the latest language features and query optimizer behaviors. This includes changes in each pre-release version Microsoft releases.
- Improvements to the way incremental statistics update thresholds are computed (140 compat mode required).
- sys.dm_exec_query_statistics_xml is added.
- We have made several performance and language enhancements to Memory-Optimized objects:
sp_spaceusedis now supported for memory-optimized tables.
sp_renameis now supported for memory-optimized tables and natively compiled T-SQL modules.
CASEstatements are now supported for natively compiled T-SQL modules.
- The limitation of 8 indexes on memory-optimized tables has been eliminated.
TOP (N) WITH TIESis now supported in natively compiled T-SQL modules.
ALTER TABLEagainst memory-optimized tables is now substantially faster in most cases.
- Transaction log redo of memory-optimized tables is now done in parallel. This bolsters faster recovery times and significantly increases the sustained throughput of AlwaysOn availability group configuration.
- Memory-optimized filegroup files can now be stored on Azure Storage. Backup/Restore of memory-optimized files on Azure Storage is also available now.
- Clustered Columnstore Indexes now support LOB columns (nvarchar(max), varchar(max), varbinary(max)).
- The STRING_AGG aggregate function has been added.
- New Permissions:
DATABASE SCOPED CREDENTIALis now a class of securable, supporting
TAKE OWNERSHIP, and
ADMINISTER DATABASE BULK OPERATIONSwhich is restricted to SQL Database is now visible in
- The sys.dm_os_host_info DMV is added to provide operating system information for both Windows and Linux.
- The database roles are created with R Services for managing permissions associated with packages. For more information, see R Package management for SQL Server.
Code Samples for new In-Memory Enhancements
The following subsections provide Transact-SQL code samples which illustrate new In-Memory features which bullet listed in preceding text in this article.
The CTP 1.1 bullet list for In-Memory is here.
Computed column in a memory-optimized table
This CREATE TABLE statement illustrates the following features which were mentioned in preceding text about CTP 1.1:
- JSON check constraint on a column.
- New computed columns.
- An index on a computed column.
CREATE TABLE Product( ProductID int PRIMARY KEY NONCLUSTERED, Name nvarchar(400) NOT NULL, Price float, Data nvarchar(4000) CONSTRAINT [Data contains JSON] CHECK (ISJSON(Data)=1), MadeIn AS CAST(JSON_VALUE(Data, '$.MadeIn') as NVARCHAR(50)) PERSISTED, Cost AS CAST(JSON_VALUE(Data, '$.ManufacturingCost') as float ), INDEX [idx_Product_MadeIn] NONCLUSTERED (MadeIn) ) WITH (MEMORY_OPTIMIZED=ON);
CROSS APPLY, and JSON functions
This CREATE PROCEDURE statement, for a natively compiled stored procedure, illustrates the following features which were mentioned in preceding text about CTP 1.1:
- CROSS APPLY operator.
- JSON functions.
CREATE OR ALTER PROCEDURE ProductList() WITH SCHEMABINDING, NATIVE_COMPILATION as begin atomic with (transaction isolation level = snapshot, language = N'English') SELECT ProductID, Name, Price, Tags, Data, JSON_VALUE(Data,'$.MadeIn') AS MadeIn, value FROM Product CROSS APPLY OPENJSON(Data, '$.SalesReasons') FOR JSON PATH end;