What's new in SQL Server 2019

  • 14 minutes to read

APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

SQL Server 2019 builds on previous releases to grow SQL Server as a platform that gives you choices of development languages, data types, on-premises or cloud, and operating systems.

This article summarizes new features and enhancements for SQL Server 2019.

For more information and known issues, see the SQL Server 2019 Release Notes.

Use the latest tools for the best experience with SQL Server 2019.

Note

The content is published for the SQL Server 2019 release candidate. The release candidate is pre-release software. The information is subject to change. For information about support scenarios, refer to Support.

This release includes improvements that were announced earlier in community technology preview (CTP) releases. The improvements added features, fixed bugs, improved security, and optimized performance. For a list of features introduced or improved in the CTP releases before SQL Server 2019 release candidate, see SQL Server 2019 CTP announcement archive.

SQL Server 2019 introduces Big Data Clusters for SQL Server. It also provides additional capability and improvements for the SQL Server database engine, SQL Server Analysis Services, SQL Server Machine Learning Services, SQL Server on Linux, and SQL Server Master Data Services.

The following sections provide an overview of these features.

SQL Server 2019 Big Data Clusters

New feature or update Details
Scalable big data solution Deploy scalable clusters of SQL Server, Spark, and HDFS containers running on Kubernetes

Read, write, and process big data from Transact-SQL or Spark

Easily combine and analyze high-value relational data with high-volume big data

Query external data sources

Store big data in HDFS managed by SQL Server

Query data from multiple external data sources through the cluster

Use the data for AI, machine learning, and other analysis tasks

Deploy and run applications in Big Data Clusters
   

For more details, see What are SQL Server Big Data Clusters.

SQL Server 2019 (CTP) announcement archive contains a list of features announced and changed for all previous CTP releases of this feature.

Note

To experience SQL Server 2019 Big Data Clusters, use the bits provided for SQL Server 2019 CTP 3.2. Release candidate bits for SQL Server 2019 Big Data Clusters will be available soon.

Database engine

Security

New feature or update Details
Index encrypted columns Create indexes on columns encrypted using randomized encryption and enclave-enabled keys, to improve the performance of rich queries (using LIKE and comparison operators). See Always Encrypted with Secure Enclaves.
Suspend and resume initial scan for Transparent Data Encryption (TDE) See Transparent Data Encryption (TDE) scan - suspend and resume.
Certificate management in SQL Server Configuration Manager See Certificate Management (SQL Server Configuration Manager).
   

Graph

New feature or update Details
Edge constraint cascade delete actions Define cascaded delete actions on an edge constraint in a graph database. See Edge constraints.
New graph function - SHORTEST_PATH Use SHORTEST_PATH inside MATCH to find the shortest path between any 2 nodes in a graph or to perform arbitrary length traversals.
Partition tables and indexes The data of partitioned tables and indexes is divided into units that can be spread across more than one filegroup in a graph database.
Use derived table or view aliases in graph match query See Graph match query.
   

Indexes

New feature or update Details
OPTIMIZE_FOR_SEQUENTIAL_KEY Turns on an optimization within the SQL Server Database Engine that helps improve throughput for high-concurrency inserts into the index. This option is intended for indexes that are prone to last-page insert contention, typically seen with indexes that have a sequential key such as an identity column, sequence, or date/time column. See CREATE INDEX for more information.
Build and rebuild online clustered columnstore index See Perform Index Operations Online.
   

In memory databases

New feature or update Details
DDL control for hybrid buffer pool With hybrid buffer pool, database pages sitting on database files placed on a persistent memory (PMEM) device will be directly accessed when required.
   

Unicode support

New feature or update Details
Support for UTF-8 character encoding Support UTF-8 character for import and export encoding, and as database level or column level collation for string data. This supports applications extending to a global scale, where the requirement of providing global multilingual database applications and services is critical to meet customer demands, and specific market regulations. See Collation and Unicode Support

[SQL Server 2019 release candidate enables UTF-8 support for Polybase external tables, and for Always Encrypted.
   

PolyBase

New feature or update Details
Query external tables External table column names are now used for querying SQL Server, Oracle, Teradata, MongoDB & ODBC data sources. See What is PolyBase.
Support for UTF-8 character encoding Support UTF-8 character with external tables. See Collation and Unicode Support
   

Server settings

New feature or update Details
Hybrid buffer pool New feature of the SQL Server Database Engine where database pages sitting on database files placed on a persistent memory (PMEM) device will be directly accessed when required. See Hybrid buffer pool.
   

Performance monitoring

New feature or update Details
WAIT_ON_SYNC_STATISTICS_REFRESH New wait type in sys.dm_os_wait_stats dynamic management view. It shows the accumulated instance-level time spent on synchronous statistics refresh operations. See sys.dm_os_wait_stats.
Custom capture policy for the Query Store When enabled, additional Query Store configurations are available under a new Query Store Capture Policy setting, to fine-tune data collection in a specific server. For more information, see ALTER DATABASE SET Options.
LIGHTWEIGHT_QUERY_PROFILING New database scoped configuration. See LIGHTWEIGHT_QUERY_PROFILING.
sys.dm_exec_requests column command Shows SELECT (STATMAN) if a SELECT is waiting for a synchronous statistics update operation to complete prior to continuing query execution. See sys.dm_exec_requests.
sys.dm_exec_query_plan_stats New DMF returns the equivalent of the last known actual execution plan for most queries. See sys.dm_exec_query_plan_stats.
LAST_QUERY_PLAN_STATS New database scoped configuration to enable sys.dm_exec_query_plan_stats. See ALTER DATABASE SCOPED CONFIGURATION.
query_post_execution_plan_profile Extended Event collects the equivalent of an actual execution plan based on lightweight profiling, unlike query_post_execution_showplan which uses standard profiling. See Query profiling infrastructure.
   

Language extensions

New feature or update Details
New Java language SDK Simplifies development of Java programs that can be run from SQL Server. See Microsoft Extensibility SDK for Java for SQL Server.
Java language SDK is open source The Microsoft Extensibility SDK for Java for Microsoft SQL Server is now open source and available on GitHub.
Support for Java data types See Java data types.
New default Java Runtime SQL Server now includes Azul Systems Zulu Embedded for Java support throughout the product. For more information, see Free supported Java in SQL Server 2019 is now available.
SQL Server language extensions Execute external code with the extensibility framework. See SQL Server language extensions.
Register external languages New DDL, CREATE EXTERNAL LANGUAGE, registers external languages, like Java, in SQL Server. See CREATE EXTERNAL LANGUAGE.
   

Spatial

New feature or update Details
New spatial reference identifiers (SRIDs) Australian GDA2020 provides more robust and accurate datum which is more closely aligned to global positioning systems. The new SRIDs are :

- 7843 for geographic 2D
- 7844 for geographic 3D

sys.spatial_reference_systems view contains definitions of new SRIDs.
   

Performance

New feature or update Details
Accelerated database recovery Enable accelerated database recovery per-database. See Accelerated database recovery.
Forcing fast forward and static cursors Query Store plan forcing support for fast forward and static cursors. See Plan forcing support for fast forward and static cursors.
Reduced recompilations for workloads Improves using temporary tables across multiple scopes. See Reduced recompilations for workloads
Indirect checkpoint scalability See Improved indirect checkpoint scalability.
Memory-optimized tempdb metadata SQL Server 2019 introduces a new feature that is part of the In-Memory Database feature family, memory-optimized tempdb metadata, which effectively removes this bottleneck and unlocks a new level of scalability for tempdb heavy workloads. In SQL Server 2019, the system tables involved in managing temp table metadata can be moved into latch-free non-durable memory-optimized tables. See Memory-Optimized tempdb Metadata.
Concurrent PFS updates PFS pages are special pages within a database file that SQL Server uses to help locate free space when allocating space for an object. Page latch contention on PFS pages is something that is commonly associated with tempdb, but it can also occur on user databases when there are many concurrent object allocation threads. This improvement changes the way that concurrency is managed with PFS updates so that they can be updated under a shared latch, rather than an exclusive latch. This behavior is on by default in all databases (including tempdb) starting with SQL Server 2019.
Row mode memory grant feedback Expands on the batch mode memory grant feedback feature by adjusting memory grant sizes for both batch and row mode operators. This can automatically correct excessive grants that result in wasted memory and reduced concurrency, and correct insufficient memory grants that cause expensive spills to disk. See Row mode memory grant feedback.
Table variable deferred compilation Improves plan quality and overall performance for queries that reference table variables. During optimization and initial compilation, this feature propagates cardinality estimates that are based on actual table variable row counts. This accurate row count information optimizes downstream plan operations. See Table variable deferred compilation.
APPROX_COUNT_DISTINCT For scenarios when absolute precision isn't important, but responsiveness is critical, APPROX_COUNT_DISTINCT aggregates across large datasets using less resources than COUNT(DISTINCT()) for superior concurrency. See Approximate query processing.
Batch mode on rowstore Batch mode on rowstore enables batch mode execution without requiring columnstore indexes. Batch mode execution uses CPU more efficiently during analytical workloads, but until SQL Server 2019 it was only used when a query included operations with columnstore indexes. However, some applications may use features that aren't supported with columnstore indexes, and therefore could not leverage batch mode. Starting with SQL Server 2019, batch mode is enabled on eligible analytical worloads whose queries include operations with any type of index (rowstore or columnstore). See Batch mode on rowstore.
Scalar UDF Inlining Automatically transforms scalar UDFs into relational expressions and embeds them in the calling SQL query. This transformation improves the performance of workloads that take advantage of scalar UDFs. Scalar UDF Inlining.
   

Availability groups

New feature or update Details
Up to five synchronous replicas SQL Server 2019 increases the maximum number of synchronous replicas to 5, up from 3 in SQL Server 2017 (14.x). You can configure this group of five replicas to have automatic failover within the group. There is one primary replica, plus four synchronous secondary replicas.
Secondary-to-primary replica connection redirection Allows client application connections to be directed to the primary replica regardless of the target server specified in the connection string. For details, see Secondary to primary replica read/write connection redirection (Always On Availability Groups).
   

Setup

New feature or update Details
New memory setup options Sets the min server memory (MB) and max server memory (MB) server configurations during installation. For more information, see Database Engine Configuration - Memory page and the USESQLRECOMMENDEDMEMORYLIMITS, SQLMINMEMORY, and SQLMAXMEMORY parameters in Install SQL Server from the Command Prompt. The proposed value will align with the memory configuration guidelines in Server Memory Configuration Options.
New parallelism setup options Sets the max degree of parallelism server configuration during installation. For more information, see Database Engine Configuration - MaxDOP page and the SQLMAXDOP parameter in Install SQL Server from the Command Prompt. The default value will align with the max degree of parallelism guidelines in Configure the max degree of parallelism Server Configuration Option.
   

Error messages

New feature or update Details
Verbose truncation warnings Truncation error message defaults to include table and column names, and truncated value. See VERBOSE_TRUNCATION_WARNINGS.
   

SQL Server on Linux

New feature or update Details
New container registry Get started with SQL Server containers on Docker
Replication support SQL Server Replication on Linux
Support for the Microsoft Distributed Transaction Coordinator (MSDTC) How to configure MSDTC on Linux
OpenLDAP support for third-party AD providers Tutorial: Use Active Directory authentication with SQL Server on Linux
Machine Learning on Linux Configure Machine Learning on Linux
tempdb improvements By default, a new installation of SQL Server on Linux creates multiple tempdb data files based on the number of logical cores (with up to 8 data files). This does not apply to in-place minor or major version upgrades. Each tempdb file is 8 MB with an auto growth of 64 MB. This behavior is similar to the default SQL Server installation on Windows.
PolyBase on Linux Install PolyBase on Linux for non-Hadoop connectors.

PolyBase type mapping.
Change Data Capture (CDC) support Change Data Capture (CDC) is now supported on Linux for SQL Server 2019.
   

Setup

New feature or update Details
New memory setup options Sets the min server memory (MB) and max server memory (MB) server configurations during installation. For more information, see the USESQLRECOMMENDEDMEMORYLIMITS, SQLMINMEMORY, and SQLMAXMEMORY parameters in Install SQL Server from the Command Prompt. The proposed value will align with the memory configuration guidelines in Server Memory Configuration Options.
New parallelism setup options Sets the max degree of parallelism server configuration during installation. For more information, see the SQLMAXDOP parameter in Install SQL Server from the Command Prompt. The default value will align with the max degree of parallelism guidelines in Configure the max degree of parallelism Server Configuration Option.
   

SQL Server Machine Learning Services

New feature or update Details
Partition-based modeling Process external scripts per partition of your data using the new parameters added to sp_execute_external_script. This functionality supports training many small models (one model per partition of data) instead of one large model. See Create partition-based models
Windows Server Failover Cluster Configure high availability for Machine Learning Services on a Windows Server Failover Cluster.
   

Master Data Services

New feature or update Details
Supports Azure SQL Database managed instance databases. Host Master Data Services on a managed instance. See Master Data Services installation and configuration.
New HTML controls HTML controls replace all former Silverlight components. Silverlight dependency removed.
   

Analysis Services

New feature or update Details
Query interleaving See Query interleaving
MDX query support for tabular models with calculation groups See Calculation groups.
Calculation groups in tabular model Calculation groups in tabular model
MDX query support for tabular models with calculation groups See Calculation groups.
Dynamic formatting of measures using calculation groups This feature allows you to conditionally change format strings for measures with calculation groups. For example, with currency conversion, a measure can be displayed using different foreign currency formats.
Many-to-many relationships in tabular models Many-to-many relationships in tabular models
Property settings for resource governance Property settings for resource governance
Governance setting for Power BI cache refreshes. The Power BI service caches dashboard tile data and report data for initial load of Live Connect report, causing an excessive number of cache queries being submitted to SSAS, and in extreme cases overload the server. This release introduces the ClientCacheRefreshPolicy property. This property allows you to override this behavior at the server level. To learn more, see General Properties.
Online attach This feature provides the ability to attach a tabular model as an online operation. Online attach can be used for synchronization of read-only replicas in on-premises query scale-out environments. To learn more see Online attach in Details.
   

Enabled deployment scenarios

SQL Server 2019 release candidate (RC) enables the following scenarios:

  • Side-by-side installation. Install instances of SQL Server 2019 RC with instances of SQL Server 2012 through SQL Server 2017, or other instances SQL Server 2019 CTP 3.0 or higher.

    Note

    While side-by-side is not blocked with SQL Server 2008 and 2008 R2, there are no commonly supported Windows Operating system versions between them and SQL Server 2019.

  • In-place upgrade. Upgrade instances of SQL Server 2019 RC from instances of SQL Server 2012 through SQL Server 2017 and SQL Server CTP 3.0. Upgrade from a SQL Server 2019 CTP below 3.0 is not supported, a new installation must be performed.

    Note

    While in-place upgrade from SQL Server 2008 and 2008 R2 is not blocked, there are no commonly supported Windows Operating system versions between them and SQL Server 2019.

Support

SQL Server 2019 RC is preview software. It is not publicly supported for operations. Customers who are in the SQL Early Adopter Program, may have support to run SQL Server 2019 RC by special agreement, in consultation with Microsoft.

Limited support for customers not in the early adoption program may be found at one of the following locations:

Try SQL Server 2019!

For specific features excluded from support, see the release notes.

In addition, the following features are added or enhanced for SQL Server 2019 CTP 3.2.

See also

Next steps

info_tip Get Help