What's new in SQL Server 2019
SQL Server 2019 preview 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 what is new for SQL Server 2019. For more information and known issues, see the SQL Server 2019 Release Notes.
Try SQL Server 2019!
- Download SQL Server 2019 to install on Windows
- Install on Linux for Red Hat Enterprise Server, SUSE Linux Enterprise Server, and Ubuntu.
- Run on SQL Server 2019 on Docker.
Community technology preview (CTP) 2.0 is the first public release of SQL Server 2019 preview. The following features are added or enhanced for SQL Server 2019 preview CTP 2.0.
- Deploy a Big Data cluster with SQL and Spark Linux containers on Kubernetes
- Access your big data from HDFS
- Run Advanced analytics and machine learning with Spark
- Use Spark streaming to data to SQL data pools
- Use Azure Data Studio to run Query books that provide a notebook experience
- UTF-8 support
- Resumable online index create allows index create to resume after interruption
- Clustered columnstore online index build and rebuild
- Always Encrypted with secure enclaves
- Intelligent query processing
- Java language programmability extension
- SQL Graph features
- Database scoped configuration setting for online and resumable DDL operations
- Always On Availability Groups - secondary replica connection redirection
- Data discovery and classification - natively built into SQL Server
- Expanded support for persistent memory devices
- Support for columnstore statistics in
- New options added to
- SQL Server Machine Learning Services failover clusters
- Lightweight query profiling infrastructure enabled by default
- New Polybase connectors
sys.dm_db_page_infosystem function returns page information
- Replication support
- Support for the Microsoft Distributed Transaction Coordinator (MSDTC)
- Always On Availability Group on Docker containers with Kubernetes
- OpenLDAP support for third-party AD providers
- Machine Learning on Linux
- New container registry
- New RHEL-based container images
- Memory pressure notification
- Silverlight controls replaced
- Certificate management in SQL Server Configuration Manager
- SQL Server Management Studio (SSMS) 18.0 (preview)
- Azure Data Studio
Continue reading for more details about these features.
Big Data Clusters
SQL Server 2019 preview Big Data Clusters enables new scenarios including the following:
- Deploy a Big Data cluster with SQL Server and Spark Linux containers on Kubernetes
- Access your big data from HDFS
- Run Advanced analytics and machine learning with Spark
- Use Spark streaming to data to SQL data pools
- Run Query books that provide a notebook experience in Azure Data Studio.
SQL Server big data clusters is first available as a limited public preview through the SQL Server 2019 Early Adoption Program. To request access, register here, and specify your interest to try SQL Server big data clusters. Microsoft will triage all requests and respond as soon as possible.
CTP 2.0 introduces or enhances the following new features for SQL Server Database Engine.
Database compatibility level
Database COMPATIBILITY_LEVEL 150 is added. To enable for a specific user database, execute:
ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = 150;
Full support for the widely used UTF-8 character encoding as an import or export encoding, or as database-level or column-level collation for text data. UTF-8 is allowed in the
VARCHAR datatypes, and is enabled when creating or changing an object’s collation to a collation with the
LATIN1_GENERAL_100_CI_AS_SC_UTF8. UTF-8 is only available to Windows collations that support supplementary characters, as introduced in SQL Server 2012.
NVARCHAR allow UTF-16 encoding only, and remain unchanged.
This feature may provide significant storage savings, depending on the character set in use. For example, changing an existing column data type with ASCII strings from
CHAR(10) using an UTF-8 enabled collation, translates into nearly 50% reduction in storage requirements. This reduction is because
NCHAR(10) requires 22 bytes for storage, whereas
CHAR(10) requires 12 bytes for the same Unicode string.
Resumable online index create
Resumable online index create allows an index create operation to pause and resume later from where the operation was paused or failed, instead of restarting from the beginning.
Resumable online index create supports the follow scenarios:
- Resume an index create operation after an index create failure, such as after a database failover or after running out of disk space.
- Pause an ongoing index create operation and resume it later allowing to temporarily free system resources as required and resume this operation later.
- Create large indexes without using as much log space and a long-running transaction that blocks other maintenance activities and allowing log truncation.
In case of an index create failure, without this feature an online index create operation must be executed again and the operation must be restarted from the beginning.
With this release, we extend the resumable functionality adding this feature to available resumable online index rebuild.
In addition, this feature can be set as the default for a specific database using database scoped default setting for online and resumable DDL operations.
For more information, see Resumable Online Index Create.
Build and rebuild clustered columnstore indexes online
Convert row-store tables into columnstore format. Creating clustered columnstore indexes (CCI) was an offline process in the previous versions of SQL Server - requiring all changes stop while the CCI is created. With SQL Server 2019 preview and Azure SQL Database you can create or re-create CCI online. Workload will not be blocked and all changes made on the underlying data are transparently added into the target columnstore table. Examples of new Transact-SQL statements that can be used are:
CREATE CLUSTERED COLUMNSTORE INDEX cci ON <tableName> WITH (ONLINE = ON);
ALTER INDEX cci ON <tableName> REBUILD WITH (ONLINE = ON);
Always Encrypted with secure enclaves
Expands upon Always Encrypted with in-place encryption and rich computations. The expansions come from the enabling of computations on plaintext data, inside a secure enclave on the server side.
Cryptographic operations include the encryption of columns, and the rotating of column encryption keys. These operations can now be issued by using Transact-SQL, and they do not require that data be moved out of the database. Secure enclaves provide Always Encrypted to a broader set of scenarios that have both of the following requirements:
- The demand that sensitive data are protected from high-privilege, yet unauthorized users, including database administrators, system administrators, cloud operators, or malware.
- The requirement that rich computations on protected data be supported within the database system.
For details, see Always Encrypted with secure enclaves.
Always Encrypted with secure enclaves is only available on Windows OS.
Intelligent query processing
Row mode memory grant feedback expands on the memory grant feedback feature introduced in SQL Server 2017 (14.x) by adjusting memory grant sizes for both batch and row mode operators. For an excessive memory grant condition, if the granted memory is more than two times the size of the actual used memory, memory grant feedback will recalculate the memory grant. Consecutive executions will then request less memory. For an insufficiently sized memory grant that results in a spill to disk, memory grant feedback will trigger a recalculation of the memory grant. Consecutive executions will then request more memory. This feature is enabled by default under database compatibility level 150.
Approximate COUNT DISTINCT returns the approximate number of unique non-null values in a group. This function is designed for use in big data scenarios. This function is optimized for queries where all the following conditions are true:
- Accesses data sets of at least millions of rows.
- Aggregates a column or columns that have a large number of distinct values.
- Responsiveness is more critical than absolute precision.
APPROX_COUNT_DISTINCTreturns results that are typically within 2% of the precise answer.
- And it returns the approximate answer in a small fraction of the time needed for the precise answer.
Batch mode on rowstore no longer requires a columnstore index to process a query in batch mode. Batch mode allows query operators to work on a set of rows, instead of just one row at a time. This feature is enabled by default under database compatibility level 150. Batch mode improves the speed of queries that access rowstore tables when all the following are true:
- The query uses analytic operators such as joins or aggregation operators.
- The query involves 100,000 or more rows.
- The query is CPU bound, rather than input/output data bound.
- Creation and use of a columnstore index would have one of the following drawbacks:
- Would add too much overhead to the query.
- Or, is not feasible because your application depends on a feature that is not yet supported with columnstore indexes.
Table variable deferred compilation improves plan quality and overall performance for queries referencing table variables. During optimization and initial compilation, this feature will propagate cardinality estimates that are based on actual table variable row counts. This accurate row count information will be used for optimizing downstream plan operations. This feature is enabled by default under database compatibility level 150.
To use intelligent query processing features, set database
COMPATIBILITY_LEVEL = 150.
Java language programmability extensions
- Java language extension (preview): Use the Java language extension to execute Java code in SQL Server. In SQL Server 2019 preview, this extension is installed when you add the feature 'Machine Learning Services (in-database)' to your SQL Server instance.
SQL Graph features
Match support in
MERGEDML allows you to specify graph relationships in a single statement, instead of separate
DELETEstatements. Merge your current graph data from node or edge tables with new data using the
MATCHpredicates in the
MERGEstatement. This feature enables
UPSERTscenarios on edge tables. Users can now use a single merge statement to insert a new edge or update an existing one between two nodes.
Edge Constraints are introduced for edge tables in SQL Graph. Edge tables can connect any node to any other node in the database. With introduction of edge constraints, you can now apply some restrictions on this behavior. The new
CONNECTIONconstraint can be used to specify the type of nodes a given edge table will be allowed to connect to in the schema.
Database scoped default setting for online and resumable DDL operations
Database scoped default setting for online and resumable DDL operations allows a default behavior setting for
RESUMABLEindex operations at the database level, rather than defining these options for each individual index DDL statement such as index create or rebuild.
Set these defaults using the
ELEVATE_RESUMABLEdatabase scoped configuration options. Both options will cause the engine to automatically elevate supported operations to index online or resumable execution. You can enable the following behaviors using these options:
FAIL_UNSUPPORTEDoption allows all index operations online or resumable and fail index operations that are not supported for online or resumable.
WHEN_SUPPPORTEDoption allows supported operations online or resumable and run index unsupported operations offline or non-resumable.
OFFoption allows the current behavior of executing all index operations offline and non-resumable unless explicitly specified in the DDL statement.
To override the default setting, include the ONLINE or RESUMABLE option in the index create and rebuild commands.
Without this feature you have to specify the online and resumable options directly in the index DDL statement such as index create and rebuild.
More information: For more information on index resumable operations see Resumable Online Index Create.
Always On Availability Groups - more synchronous replicas
Up to five synchronous replicas: SQL Server 2019 preview increases the maximum number of synchronous replicas to 5, up from 3 in SQL Server 2017 (14.x) . You can configure this group of 5 replicas to have automatic failover within the group. There is 1 primary replica, plus 4 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. This capability allows connection redirection without a listener. Use secondary-to-primary replica connection redirection in the following cases:
- The cluster technology does not offer a listener capability.
- A multi subnet configuration where redirection becomes complex.
- Read scale-out or disaster recovery scenarios where cluster type is
Data discovery and classification
Data discovery and classification provides advanced capabilities that are natively built into SQL Server. Classifying and labeling your most sensitive data provides the following benefits:
- Helps meet data privacy standards and regulatory compliance requirements.
- Supports security scenarios, such as monitoring (auditing), and alerting on anomalous access to sensitive data.
- Makes it easier to identify where sensitive data resides in the enterprise, so that administrators can take the right steps to secure the database.
For more information, see SQL Data Discovery and Classification.
Auditing has also been enhanced to include a new field in the audit log called
data_sensitivity_information, which logs the sensitivity classifications (labels) of the actual data that was returned by the query. For details and examples, see Add sensitivity classification.
There are no changes in terms of how audit is enabled. There is a new field added to the audit records,
data_sensitivity_information, which logs the sensitivity classifications (labels) of the actual data that was returned by the query. See Auditing access to sensitive data.
Expanded support for persistent memory devices
Any SQL Server file that is placed on a persistent memory device can now operate in enlightened mode. SQL Server directly accesses the device, bypassing the storage stack of the operating system using efficient memcpy operations. This mode improves performance because it allows low latency input/output against such devices.
- Examples of SQL Server files include:
- Database files
- Transaction log files
- In-Memory OLTP checkpoint files
- Persistent memory is also known as storage class memory.
- Persistent memory is occasionally referred to informally as pmem on some non-Microsoft websites.
For this preview release, enlightenment of files on persistent memory devices is only available on Linux. SQL Server on Windows supports persistent memory devices starting with SQL Server 2016.
Support for columnstore statistics in DBCC CLONEDATABASE
DBCC CLONEDATABASE creates a schema-only copy of a database that includes all the elements necessary to troubleshoot query performance issues without copying the data. In previous versions of SQL Server, the command did not copy the statistics necessary to accurately troubleshoot columnstore index queries and manual steps were required to capture this information. Now in SQL Server 2019 preview, DBCC CLONEDATABASE automatically captures the stats blobs for columnstore indexes, so no manual steps will be required.
New options added to sp_estimate_data_compression_savings
sp_estimate_data_compression_savings returns the current size of the requested object and estimates the object size for the requested compression state. Currently this procedure supports three options:
PAGE. SQL Server 2019 introduces two new options:
COLUMNSTORE_ARCHIVE. These new options will allow you to estimate the space savings if a columnstore index is created on the table using either standard or archive columnstore compression.
SQL Server Machine Learning Services failover clusters and partition based modeling
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.
Windows Server Failover Cluster: Configure high availability for Machine Learning Services on a Windows Server Failover Cluster.
For detailed information, see What's new in SQL Server Machine Learning Services.
Lightweight query profiling infrastructure enabled by default
The lightweight query profiling infrastructure (LWP) provides query performance data more efficiently than standard profiling technologies. Lightweight profiling is now enabled by default. It was introduced in SQL Server 2016 (13.x) SP1. Lightweight profiling offers a query execution statistics collection mechanism with an expected overhead of 2% CPU, compared with an overhead of up to 75% CPU for the standard query profiling mechanism. On previous versions, it was OFF by default. Database administrators could enable it with trace flag 7412.
For more information on lightweight profiling, see Developers Choice: Query progress – anytime, anywhere.
New Polybase connectors
- New connectors for SQL Server, Oracle, Teradata, and MongoDB: SQL Server 2019 preview introduces new connectors to external data for SQL Server, Oracle, Teradata, and MongoDB.
New sys.dm_db_page_info system function returns page information
sys.dm_db_page_info(database_id, file_id, page_id, mode) returns information about a page in a database. The function returns a row that contains the header information from the page, including the
partition_id. This function replaces the need to use
DBCC PAGE in most cases.
In order to facilitate troubleshooting of page-related waits, a new column called page_resource was also added to
sys.sysprocesses. This new column allows you to join
sys.dm_db_page_info to these views via another new system function -
sys.fn_PageResCracker. See the following script as an example:
SELECT page_info.* FROM sys.dm_exec_requests AS d CROSS APPLY sys.fn_PageResCracker(d.page_resource) AS r CROSS APPLY sys.dm_db_page_info(r.db_id, r.file_id, r.page_id,'DETAILED') AS page_info;
SQL Server on Linux
Replication support: SQL Server 2019 preview supports SQL Server Replication on Linux. A Linux virtual machine with SQL Agent can be a publisher, distributor, or subscriber.
Create the following types of publications:
Configure replication SQL Server Management Studio or use replication stored procedures.
Support for the Microsoft Distributed Transaction Coordinator (MSDTC): SQL Server 2019 on Linux supports the Microsoft Distributed Transaction Coordinator (MSDTC). For details, see How to configure MSDTC on Linux.
Always On Availability Group on Docker containers with Kubernetes: Kubernetes can orchestrate containers running SQL Server instances to provide a highly available set of databases with SQL Server Always On Availability Groups. A Kubernetes operator deploys a StatefulSet including a container with mssql-server container and a health monitor.
OpenLDAP support for third-party AD providers: SQL Server 2019 preview on Linux supports OpenLDAP, which allows third-party providers to join Active Directory.
Machine Learning on Linux: SQL Server 2019 Machine Learning Services (In-Database) is now supported on Linux. Support includes
sp_execute_external_scriptstored procedure. For instructions on how to install Machine Learning Services on Linux, see Install SQL Server 2019 Machine Learning Services R and Python support on Linux.
New container registry: All container images for SQL Server 2019 preview as well as SQL Server 2017 (14.x) are now located in the Microsoft Container Registry. Microsoft Container Registry is the official container registry for the distribution of Microsoft product containers. In addition, certified RHEL-based images are now published.
- Microsoft Container Registry:
- Certified RHEL-based container images:
- Microsoft Container Registry:
Master Data Services (MDS)
- Silverlight controls replaced with HTML: The Master Data Services (MDS) portal no longer depends on Silverlight. All the former Silverlight components have been replaced with HTML controls.
Certificate management in SQL Server Configuration Manager: SSL/TLS certificates are widely used to secure access to SQL Server instances. Certificate management is now integrated into the SQL Server Configuration Manager, simplifying common tasks such as:
- Viewing and validating certificates installed in a SQL Server instance.
- Viewing certificates close to expiration.
- Deploy certificates across machines participating in Always On Availability Groups (from the node holding the primary replica).
- Deploy certificates across machines participating in a failover cluster instance (from the active node).
User must have administrator permissions on all the cluster nodes.
Azure Data Studio: Previously released under the preview name SQL Operations Studio, Azure Data Studio is a lightweight, modern, open source, cross-platform desktop tool for the most common tasks in data development and administration. With Azure Data Studio you can connect to SQL Server on premises and in the cloud on Windows, macOS, and Linux. Azure Data Studio allows you to:
- Edit and run queries in a modern development environment with lightning fast Intellisense, code snippets, and source control integration.
- Quickly visualize data with built-in charting of your result sets.
- Create custom dashboards for your servers and databases using customizable widgets.
- Easily manage your broader environment with the built-in terminal.
- Analyze data in an integrated notebook experience built on Jupyter.
- Enhance your experience with custom theming and extensions.
- And explore your Azure resources with a built-in subscription and resource browser.
- Supports scenarios using SQL Server Big Data Cluster.
- Support for SQL Server 2019 preview.
- Support for Always Encrypted with secure enclaves.
- Smaller download size.
- Now based on the Visual Studio 2017 Isolated Shell.
- For a complete list, see the SSMS changelog.
SQL Server 2019 preview CTP 2.0 does not introduce new features for the following services:
- SQL Server Analysis Services (SSAS)
- SQL Server Integration Services (SSIS)
- SQL Server Reporting Services (SSRS)
Microsoft SQL Server 2019: Technical white paper
Published in September 2018. Applies to Microsoft SQL Server 2019 CTP 2.0 for Windows, Linux, and Docker containers.
- All the ways to get help
- UserVoice - Suggestion to improve SQL Server?
- Setup and Upgrade - MSDN Forum
- SQL Server Data Tools - MSDN forum
- Transact-SQL - MSDN forum
- SQL Server Security - MSDN Forum
- DBA Stack Exchange (tag sql-server) - ask SQL Server questions
- Stack Overflow (tag sql-server) - also has some answers about SQL development
- Reddit - general discussion about SQL Server
- Microsoft SQL Server License Terms and Information
- Support options for business users
- Contact Microsoft
- Edit SQL Docs
- SQL Docs Feedback
- SQL Docs Survey