SQL Server 2019 (15.x) builds on previous releases to grow SQL Server as a platform that gives you choices of development languages, data types, on-premises or cloud environments, and operating systems.
This article summarizes the new features and enhancements for SQL Server 2019 (15.x).
For the best experience with SQL Server 2019 (15.x), use the latest tools.
SQL Server 2019 (15.x) 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 video provides a 13-minute introduction into SQL Server 2019:
The following sections provide an overview of these features.
Data virtualization and SQL Server 2019 Big Data Clusters
Businesses today often preside over vast data estates consisting of a wide array of ever-growing data sets that are hosted in siloed data sources across the company. Gain near real-time insights from all your data with SQL Server 2019 Big Data Clusters, which provide a complete environment for working with large sets of data, including machine learning and AI capabilities.
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. The SQL Server master instance provides high availability and disaster recovery for all databases by using Always On availability group technology.
Data virtualization with PolyBase
Query data from external SQL Server, Oracle, Teradata, MongoDB, and ODBC data sources with external tables, now with UTF-8 encoding support. For more information, see What is PolyBase?.
SQL Server 2019 (15.x) Cumulative update 19 now introduces support for Oracle TNS files. Please refer to CREATE EXTERNAL DATA SOURCE for more information and samples.
SQL Server 2019 (15.x) builds on innovations in previous versions to provide industry-leading performance out of the box. From Intelligent Query Processing to support for persistent memory devices, the SQL Server Intelligent Database features improve performance and scalability of all your database workloads without any changes to your application or database design.
Intelligent Query Processing
With Intelligent Query Processing, you know that critical parallel workloads improve when they're running at scale. At the same time, they remain adaptive to the constantly changing world of data. Intelligent Query Processing is available by default on the latest database compatibility level setting, delivering broad impact that improves the performance of existing workloads with minimal implementation effort.
New feature or update
Details
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 adjustment can automatically correct excessive grants, which result in wasted memory and reduced concurrency. It can also correct insufficient memory grants that cause expensive spills to disk. See Row mode memory grant feedback.
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 (15.x), it was used only when a query included operations with columnstore indexes. However, some applications might use features that aren't supported with columnstore indexes and, therefore, can't use batch mode. Starting with SQL Server 2019 (15.x), batch mode is enabled on eligible analytical workloads 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. See Scalar UDF Inlining.
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.
Approximate query processing with APPROX_COUNT_DISTINCT
For scenarios when absolute precision isn't important but responsiveness is critical, APPROX_COUNT_DISTINCT aggregates across large datasets while using fewer resources than COUNT(DISTINCT()) for superior concurrency. See Approximate query processing.
In-Memory Database
SQL Server In-Memory Database technologies use modern hardware innovation to deliver unparalleled performance and scale. SQL Server 2019 (15.x) builds on earlier innovations in this area, such as in-memory online transaction processing (OLTP), to unlock a new level of scalability across all your database workloads.
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.
Memory-optimized TempDB metadata
SQL Server 2019 (15.x) 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 (15.x), the system tables involved in managing temporary table metadata can be moved into latch-free non-durable memory-optimized tables. See Memory-Optimized TempDB Metadata.
In-Memory OLTP support for Database Snapshots
SQL Server 2019 (15.x) introduces support for creating Database Snapshots of databases that include memory-optimized filegroups.
Intelligent performance
SQL Server 2019 (15.x) builds on Intelligent Database innovations in previous releases to ensure that it just runs faster. These improvements help overcome known resource bottlenecks and provide options for configuring your database server to provide predictable performance across all your workloads.
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, which is typically seen with indexes that have a sequential key, such as an identity column, sequence, or date/time column. See CREATE INDEX.
The configurable value for the REQUEST_MAX_MEMORY_GRANT_PERCENT option of CREATE WORKLOAD GROUP and ALTER WORKLOAD GROUP has been changed from an integer to a float data type, to allow more granular control of memory limits. See ALTER WORKLOAD GROUP and CREATE WORKLOAD GROUP.
Reduced recompilations for workloads
Improves performance when using temporary tables across multiple scopes by reducing unnecessary recompilations. See Reduced recompilations for workloads.
Page Free Space (PFS) pages are special pages within a database file that SQL Server uses to help locate free space when it allocates space for an object. Page latch contention on PFS pages 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 (15.x).
Scheduler worker migration
Worker migration allows an idle scheduler to migrate a worker from the runnable queue of another scheduler on the same NUMA node and immediately resume the task of the migrated worker. This enhancement provides more balanced CPU usage in situations where long-running tasks happen to be assigned to the same scheduler. See SQL Server 2019 Intelligent Performance - Worker Migration for more information.
Monitor
Monitoring improvements unlock performance insights over any database workload, just when you need them.
New feature or update
Details
WAIT_ON_SYNC_STATISTICS_REFRESH
A 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 Query Store
When this policy is enabled, additional Query Store configurations are available under a new Query Store Capture Policy setting, to fine-tune data collection in a specific server. See ALTER DATABASE SET options.
Shows SELECT (STATMAN) if a SELECT is waiting for a synchronous statistics update operation to finish before it continues the query execution. See sys.dm_exec_requests.
sys.dm_exec_query_plan_stats
A new dynamic management function (DMF) that returns the equivalent of the last known actual execution plan for all queries. See sys.dm_exec_query_plan_stats.
An extended event that collects the equivalent of an actual execution plan that's based on lightweight profiling, unlike query_post_execution_showplan, which uses standard profiling. See Query profiling infrastructure.
SQL Server 2019 (15.x) continues to provide a world-class developer experience with enhancements to graph and spatial data types, UTF-8 support, and a new extensibility framework that allows developers to use the language of their choice to gain insights across all their data.
Graph
New feature or update
Details
Edge constraint cascade delete actions
You can now define cascaded delete actions on an edge constraint in a graph database. See Edge constraints.
New graph function - SHORTEST_PATH
You can now use SHORTEST_PATH inside MATCH to find the shortest path between any two nodes in a graph or to perform arbitrary length traversals.
Partition tables and indexes
Graph tables now support table and index partitioning.
Use derived table or view aliases in graph match query
Support businesses across different countries and regions, where the requirement of providing global multilingual database applications and services is critical to meeting customer demands and complying with specific market regulations.
New feature or update
Details
Support for UTF-8 character encoding
Supports UTF-8 for import and export encoding, and as database-level or column-level collation for string data. Support includes PolyBase external tables, and Always Encrypted (when not used with Enclaves). See Collation and Unicode Support.
A new Data Definition Language (DDL), CREATE EXTERNAL LANGUAGE, registers external languages, such as Java, in SQL Server. See CREATE EXTERNAL LANGUAGE.
Spatial
New feature or update
Details
New spatial reference identifiers (SRIDs)
Australian GDA2020 provides a more robust and accurate datum that's more closely aligned with global positioning systems. The new SRIDs are:
When an extract, transform, and load (ETL) process fails because the source and the destination don't have matching data types and/or length, troubleshooting used to be time-consuming, especially in large datasets. SQL Server 2019 (15.x) allows faster insights into data truncation errors.
New feature or update
Details
Verbose truncation warnings
The data truncation error message defaults to include table and column names, and the truncated value. See VERBOSE_TRUNCATION_WARNINGS.
Mission-critical security
SQL Server provides a security architecture that's designed to allow database administrators and developers to create secure database applications and counter threats. Each version of SQL Server has improved on previous versions with the introduction of new features and functionality, and SQL Server 2019 (15.x) continues to build on this story.
New feature or update
Details
Always Encrypted with secure enclaves
Expands upon Always Encrypted with in-place encryption and rich computations by enabling computations on plaintext data inside a server-side secure enclave. In-place encryption improves the performance and the reliability of cryptographic operations (encrypting columns, rotating columns, encryption keys, and so on), because it avoids moving data out of the database.
Support for rich computations (pattern matching and comparison operations) unlocks Always Encrypted to a much broader set of scenarios and applications that demand sensitive data protection, while also requiring richer functionality in Transact-SQL queries. See Always Encrypted with Secure Enclaves.
Certificate management in SQL Server Configuration Manager
Data Discovery & Classification provides capabilities for classifying and labeling columns in user tables. Classifying sensitive data (business, financial, healthcare, personally identifiable information (PII), etc.) can play a pivotal role in an organizational information protection stature. It can serve as infrastructure for:
Helping meet data privacy standards and regulatory compliance requirements
Various security scenarios, such as monitoring (auditing) and alerting on anomalous access to sensitive data
Making it easier to identify where sensitive data resides in the enterprise so admins can take the right steps securing the database
SQL Server Audit
Auditing has also been enhanced to include a new field data_sensitivity_information in the audit log record, which contains the sensitivity classifications (labels) of the actual data that was returned by the query. For details and examples, see ADD SENSITIVITY CLASSIFICATION.
High availability
One common task that everyone who deploys SQL Server has to account for is making sure that all mission critical SQL Server instances and the databases within them are available whenever the business and end users need them. Availability is a key pillar of the SQL Server platform, and SQL Server 2019 (15.x) introduces many new features and enhancements that allow businesses to ensure that their database environments are highly available.
Availability Groups
New feature or update
Details
Up to five synchronous replicas
SQL Server 2019 (15.x) 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's one primary replica, plus four synchronous secondary replicas.
Every Software Assurance customer of SQL Server will be able to use three enhanced benefits for any SQL Server release that is still supported by Microsoft. For details, see our announcement here.
Recovery
New feature or update
Details
Accelerated database recovery
Reduce the time to recover after a restart or a long-running transaction rollback with accelerated database recovery (ADR). See Accelerated database recovery.
Resumable operations
New feature or update
Details
Online clustered columnstore index build and rebuild
SQL Server 2019 (15.x) builds on the innovations that were introduced in SQL Server 2017 (14.x) to allow you to run SQL Server on your platform of choice with more functionality and security than ever before.
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 eight data files). This doesn't 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.
Change Data Capture (CDC) is now supported on Linux for SQL Server 2019 (15.x).
Containers
The easiest way to get started working with SQL Server is to use containers. SQL Server 2019 (15.x) builds on the innovations introduced in earlier versions to enable you to deploy SQL Server containers on new platforms, in a safer manner, and with more functionality.
New feature or update
Details
Microsoft Container Registry
The Microsoft Container Registry now replaces Docker Hub for new official Microsoft container images, including SQL Server 2019 (15.x).
Starting with SQL Server 2019 (15.x), you can run SQL Server containers on Red Hat Enterprise Linux.
PolyBase and Machine Learning support
SQL Server 2019 (15.x) introduces new ways to work with SQL Server Containers such as Machine Learning Services and PolyBase. Check out some examples in the SQL Server in container GitHub repository.
If an Enterprise Server/CAL license Product Key is entered, and the machine has more than 20 physical cores, or 40 logical cores when simultaneous multithreading (SMT) is enabled, a warning is shown during setup. Users can still acknowledge the limitation and continue setup, or enter a License Key that supports the operating system maximum number of processors.
SQL Server Machine Learning Services
New feature or update
Details
Partition-based modeling
You can process external scripts per partition of your data by 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
You can configure high availability for Machine Learning Services on a Windows Server Failover Cluster.
SQL Server Analysis Services
This release introduces new features and improvements for performance, resource governance, and client support.
New feature or update
Details
Calculation groups in tabular models
Calculation groups can significantly reduce the number of redundant measures by grouping common measure expressions as calculation items. To learn more, see Calculation groups in tabular model.
Query interleaving
Query interleaving is a tabular mode system configuration that can improve user query response times in high-concurrency scenarios. To learn more, see Query interleaving.
Many-to-many relationships in tabular models
Allows many-to-many relationships between tables where both columns are non-unique. To learn more, see Relationships in tabular models.
Property settings for resource governance
This release includes new memory settings: Memory\QueryMemoryLimit, DbpropMsmdRequestMemoryLimit, and OLAP\Query\RowsetSerializationLimit for resource governance. To learn more, see Memory settings.
Governance setting for Power BI cache refreshes
This release introduces the ClientCacheRefreshPolicy property, which overrides caching dashboard tile data and report data for initial load of Live connect reports by the Power BI service. To learn more, see General Properties.
Online attach
Online attach can be used for synchronization of read-only replicas in on-premises query scale-out environments. To learn more, see Online attach.
SQL Server Integration Services
This release introduces new features to improve file operations.
New feature or update
Details
Flexible file task
Perform file operations on Local File System, Azure Blob Storage, and Azure Data Lake Storage Gen2. See Flexible File Task.
HTML controls replace all former Silverlight components. Silverlight dependency removed.
SQL Server Reporting Services
This release of SQL Server Reporting Services features support for Azure SQL Managed Instance, Power BI Premium datasets, enhanced accessibility, Microsoft Entra application proxy, and transparent data encryption (TDE). It also brings an update to Microsoft Report Builder. See What's new in SQL Server Reporting Services for details.
Did you know that you can edit SQL content yourself? If you do so, not only do you help improve our documentation, but you also get credited as a contributor to the page.
Administer an SQL Server database infrastructure for cloud, on-premises and hybrid relational databases using the Microsoft PaaS relational database offerings.