What's New in SQL Server vNext (Database Engine)

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2017)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Note: SQL Server vNext 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.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 = SAFE may be able to access external system resources, call unmanaged code, and acquire sysadmin privileges. Beginning with SQL Server 2017, an sp_configure option called clr strict security is introduced to enhance the security of CLR assemblies. clr strict security is enabled by default, and treats SAFE and EXTERNAL_ACCESS assemblies as if they were marked UNSAFE. The clr strict security option 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 ASSEMBLY permission 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.

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)

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 APPLY operator in natively compiled modules.
  • New string functions CONCAT_WS, TRANSLATE, and TRIM are added.
  • The WITHIN GROUP clause 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_STORAGE option 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_spaceused is now supported for memory-optimized tables.
    • sp_rename is now supported for memory-optimized tables and natively compiled T-SQL modules.
    • CASE statements are now supported for natively compiled T-SQL modules.
    • The limitation of 8 indexes on memory-optimized tables has been eliminated.
    • TOP (N) WITH TIES is now supported in natively compiled T-SQL modules.
    • ALTER TABLE against 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 CREDENTIAL is now a class of securable, supporting CONTROL, ALTER, REFERENCES, TAKE OWNERSHIP, and VIEW DEFINITION permissions. ADMINISTER DATABASE BULK OPERATIONS which is restricted to SQL Database is now visible in sys.fn_builtin_permissions.
  • 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;