Επεξεργασία

Κοινή χρήση μέσω

What's new in SQL Server 2019 (15.x)

  • Άρθρο

Applies to: SQL Server

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 more information and known issues, see SQL Server 2019 (15.x) release notes.

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.

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.
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.

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

Intelligent Database

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.
Forcing fast forward and static cursors Provides Query Store plan forcing support for fast forward and static cursors. See Plan forcing support for fast forward and static cursors.
Resource governance 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.
Indirect checkpoint scalability See Improved indirect checkpoint scalability.
Concurrent PFS updates 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.
LIGHTWEIGHT_QUERY_PROFILING A 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 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.
LAST_QUERY_PLAN_STATS A new database-scoped configuration that enables sys.dm_exec_query_plan_stats. See ALTER DATABASE SCOPED CONFIGURATION.
query_post_execution_plan_profile 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.
sys.dm_db_page_info(database_id, file_id, page_id, mode) A new DMF that returns information about a page in a database. See sys.dm_db_page_info (Transact-SQL).

Developer experience

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 See Graph match query.

Unicode support

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.

Language extensions

New feature or update Details
New Java language SDK Simplifies the 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. 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 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:
  • 7843 for geographic 2D
  • 7844 for geographic 3D
For definitions of new SRIDs, see sys.spatial_reference_systems view.

Error messages

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 Certificate management tasks such as viewing and deploying certificates is now possible by using SQL Server Configuration Manager. See Certificate Management (SQL Server Configuration Manager).
Data Discovery & Classification 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.
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).
HADR Benefits 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 See Perform Index Operations Online.
Resumable online rowstore index build See Perform Index Operations Online.
Suspend and resume initial scan for transparent data encryption (TDE) See Transparent Data Encryption (TDE) scan - suspend and resume.

Platform choice

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.

Linux

New feature or update Details
Replication support See SQL Server Replication on Linux.
Support for the Microsoft Distributed Transaction Coordinator (MSDTC) See How to configure MSDTC on Linux.
OpenLDAP support for third-party AD providers See Tutorial: Use Active Directory authentication with SQL Server on Linux.
Machine Learning Services on Linux See Install SQL Server Machine Learning Services (Python and R) 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 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.
PolyBase on Linux See Install PolyBase on Linux for non-Hadoop connectors.

See PolyBase type mapping.
Change Data Capture (CDC) support 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).
Non-root containers SQL Server 2019 (15.x) introduces the ability to create safer containers by starting the SQL Server process as a non-root user by default. See build and run SQL Server containers as a non-root user.
Red Hat certified container images 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.

Setup options

New feature or update Details
New memory setup options Sets the min server memory (MB) and max server memory (MB) server configurations during installation. See Database Engine Configuration - Memory page and the USESQLRECOMMENDEDMEMORYLIMITS, SQLMINMEMORY, and SQLMAXMEMORY parameters in Install SQL Server from the Command Prompt. The proposed value aligns with the memory configuration guidelines in Server Memory Configuration Options.
New parallelism setup options Sets the max degree of parallelism server configuration during installation. See Database Engine Configuration - MaxDOP page and the SQLMAXDOP parameter in Install SQL Server from the Command Prompt. The default value aligns with the max degree of parallelism guidelines in Configure the max degree of parallelism Server Configuration Option.
Setup warning on Server/CAL license Product Key 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.
Flexible file source and destination Read and write data for Azure Blob Storage, and Azure Data Lake Storage Gen2. See Flexible File Source and Flexible File Destination.

SQL Server Master Data Services

New feature or update Details
Support for Azure SQL Managed Instance databases Host Master Data Services on Azure SQL Managed Instance. See Master Data Services installation and configuration.
New HTML controls 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.

Related content

Get help

Contribute to SQL documentation

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.

For more information, see How to contribute to SQL Server documentation