What's new in SQL Server 2022
Applies to: 
SQL Server 2022 (16.x)
SQL Server 2022 (16.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.
The following video introduces SQL Server 2022 (16.x).
For additional video content, see:
This article summarizes the new features and enhancements for SQL Server 2022 (16.x).
Get SQL Server 2022 (16.x)
Get SQL Server 2022 Evaluation Edition. Build number: 16.0.1000.6.
For more information and known issues, see SQL Server 2022 release notes.
For the best experience with SQL Server 2022 (16.x), use the latest tools.
Feature highlights
The following sections identify features that are improved our introduced in SQL Server 2022 (16.x).
- Analytics
- Availability
- Security
- Performance
- Query Store and intelligent query processing
- Management
- Platform
- Language
Analytics
| New feature or update | Details |
|---|---|
| Azure Synapse Link for SQL | Get near real time analytics over operational data in SQL Server 2022 (16.x). With a seamless integration between operational stores in SQL Server 2022 (16.x) and Azure Synapse Analytics dedicated SQL pools, Azure Synapse Link for SQL enables you to run analytics, business intelligence and machine learning scenarios on your operational data with minimum effect on source databases with a new change feed technology. For more information, see What is Azure Synapse Link for SQL?. See also, Limitations and known issues with Azure Synapse Link for SQL. |
| Object storage integration | SQL Server 2022 (16.x) introduces new object storage integration to the data platform, enabling you to integrate SQL Server with S3-compatible object storage, in addition to Azure Storage. The first is backup to URL and the second is Data Lake Virtualization. Data Lake Virtualization integrates PolyBase with S3-compatible object storage, and adds support for querying parquet files with T-SQL. |
| Data Virtualization | Query different types of data on different types of data sources from SQL Server. SQL Server 2022 (16.x) Cumulative update 2 now introduces support for Oracle TNS files. For more information and samples, see CREATE EXTERNAL DATA SOURCE (Transact-SQL) CONNECTION_OPTIONS. |
Availability
| New feature or update | Details |
|---|---|
| Link to Azure SQL Managed Instance | Replicate your data between your SQL Server instance to Azure SQL Managed Instance for disaster recovery and migration. See Overview of the Managed Instance link. |
| Contained availability group | Create an Always On availability group that: - Manages its own metadata objects (users, logins, permissions, SQL Agent jobs etc.) at the availability group level in addition to the instance level. - Includes specialized contained system databases within the availability group. For more information, see What is a contained availability group? |
| Distributed availability group | Now using multiple TCP connections for better network bandwidth utilization across a remote link with long tcp latencies. |
| Improved backup metadata | backupset system table returns last valid restore time. See backupset. |
Security
| New feature or update | Details |
|---|---|
| Microsoft Defender for Cloud integration | Protect your SQL servers using the Defender for SQL plan. Defender for SQL plan requires that SQL Server Extension for Azure is enabled and includes functionalities for discovering and mitigating potential database vulnerabilities and detecting anomalous activities that could indicate a threat to your databases. For more information about how Defender for SQL can protect your entire database estate, see Overview of Microsoft Defender for Azure SQL. |
| Microsoft Purview integration | Apply Microsoft Purview access policies to any SQL Server instance that is enrolled in both Azure Arc and the Microsoft Purview Data Use Management. Newly introduced SQL Performance Monitor and SQL Security Auditor roles, align with the principle of least privilege using Microsoft Purview access policies. Check out Provision access by data owner for SQL Server enabled by Azure Arc for details. |
| Ledger | The ledger feature provides tamper-evidence capabilities in your database. You can cryptographically attest to other parties, such as auditors or other business parties, that your data wasn't tampered with. See Ledger overview. |
| Microsoft Entra authentication | Use authentication with Microsoft Entra ID (formerly Azure Active Directory) to connect to SQL Server. Starting with SQL Server 2022 (16.x) Cumulative Update 6, you can configure transactional replication with Microsoft Entra authentication - generally available (GA) in Cumulative Update 12. |
| Always encrypted with secure enclaves | Support for JOIN, GROUP BY, and ORDER BY, and for text columns using UTF-8 collations in confidential queries using enclaves. Improved performance. See Always Encrypted with secure enclaves. |
| Access Control: Permissions | New granular permissions improve adherence with the Principle of Least Privilege Read here for an in-depth explanation of the revamped SQL Permission system for Principle of Least Privilege and external policies |
| Access Control: Server-level Roles | New built-in server-level roles enable least privileged access for administrative tasks that apply to the whole SQL Server Instance |
| Dynamic data masking | Granular UNMASK permissions for Dynamic Data Masking. |
| Support for PFX certificates, and other cryptographic improvements | New support for import and export of PFX file formatted certificates and private keys. Ability to backup and restore master keys to Azure Blob Storage. SQL Server-generated certificates now have a default RSA key size of 3,072 bits. Added BACKUP SYMMETRIC KEY and RESTORE SYMMETRIC KEY. See also, BACKUP CERTIFICATE. |
| Support MS-TDS 8.0 protocol | New MS-TDS protocol iteration. See TDS 8.0 support and TLS 1.3 support: - Makes encryption mandatory - Aligns MS-TDS with HTTPS making it manageable by network appliances for additional security - Removes MS-TDS / TLS custom interleaving and enables usage of TLS 1.3 and subsequent TLS protocol versions. |
Performance
| New feature or update | Details |
|---|---|
| System page latch concurrency enhancements | Concurrent updates to global allocation map (GAM) pages and shared global allocation map (SGAM) pages reduce page latch contention while allocating/deallocating data pages and extents. These enhancements apply to all user databases and especially benefit tempdb heavy workloads. |
| Buffer pool parallel scan | Improves the performance of buffer pool scan operations on large-memory machines by utilizing multiple CPU cores. Learn more about Operations that trigger a buffer pool scan might run slowly on large-memory computers. |
| Ordered clustered columnstore index | Ordered clustered columnstore index (CCI) sorts the existing data in memory before the index builder compresses the data into index segments. This has the potential of more efficient segment elimination, resulting in better performance as the number of segments to read from disk is reduced. For more information, see CREATE COLUMNSTORE INDEX and What's new in columnstore indexes. Also available in Synapse Analytics. See Query performance. |
| Improved columnstore segment elimination | All columnstore indexes benefit from enhanced segment elimination by data type. Data type choices might have a significant impact on query performance based common filter predicates for queries on the columnstore index. This segment elimination applied to numeric, date, and time data types, and the datetimeoffset data type with scale less than or equal to two. Beginning in SQL Server 2022 (16.x), segment elimination capabilities extend to string, binary, guid data types, and the datetimeoffset data type for scale greater than two. |
| In-memory OLTP management | Improve memory management in large memory servers to reduce out-of-memory conditions. |
| Virtual log file growth | In previous versions of SQL Server, if the next growth is more than 1/8 of the current log size, and the growth is less than 64 MB, four VLFs were created. In SQL Server 2022 (16.x), this behavior is slightly different. Only one VLF is created if the growth is less than or equal to 64 MB and more than 1/8 of the current log size. For more information on VLF growth, see Virtual Log Files (VLFs). |
| Thread management | - ParallelRedoThreadPool: Instance level thread pool shared with all databases having redo work. With this, each database can take the benefit of parallel redo. The parallel redo thread pool allows multiple threads to process the log records of one database at the same time (in parallel). In previous versions, the number of available threads for redo was limited to 100.- ParallelRedoBatchRedo: Redo of log records are batched under one latch improving speed. This improves recovery, catchup redo, and crash recovery redo. |
| Reduced buffer pool I/O promotions | Reduced the incidents of a single page being promoted to eight pages when populating the buffer pool from storage, causing unnecessary I/O. The buffer pool can be populated more efficiently by the read-ahead mechanism. This change was introduced in SQL Server 2022 (all editions) and included in Azure SQL Database and Azure SQL Managed Instance. |
| Enhanced spinlock algorithms | Spinlocks are a huge part of the consistency inside the engine for multiple threads. Internal adjustments to the Database Engine make spinlocks more efficient. This change was introduced in SQL Server 2022 (all editions) and included in Azure SQL Database and Azure SQL Managed Instance. |
| Improved virtual log file (VLF) algorithms | Virtual File Log (VLF) is an abstraction of the physical transaction log. Having a large number of small VLFs based on log growth can affect performance of operations like recovery. We changed the algorithm for how many VLF files we create during certain log grow scenarios. To read more about how we have changed this algorithm in SQL Server 2022 (16.x), see Virtual Log Files (VLFs). This change was introduced in SQL Server 2022 (all editions) and included in Azure SQL Database. |
| Instant file initialization for transaction log file growth events | In general, transaction log files can't benefit from instant file initialization (IFI). Starting with SQL Server 2022 (16.x) (all editions) and in Azure SQL Database, instant file initialization can benefit transaction log growth events up to 64 MB. The default auto growth size increment for new databases is 64 MB. Transaction log file autogrowth events larger than 64 MB can't benefit from instant file initialization. For more information, see Database instant file initialization. |
Query Store and intelligent query processing
The intelligent query processing (IQP) feature family includes features that improve the performance of existing workloads with minimal implementation effort.
| New feature or update | Details |
|---|---|
| Query Store on secondary replicas | Query Store on secondary replicas enables the same Query Store functionality on secondary replica workloads that is available for primary replicas. Learn more in Query Store for secondary replicas. For more information, see Query Store improvements later in this article. |
| Query Store hints | Query Store hints use the Query Store to provide a method to shape query plans without changing application code. Previously only available on Azure SQL Database and Azure SQL Managed Instance, Query Store hints are now available in SQL Server 2022 (16.x). Requires the Query Store to be enabled and in "Read write" mode. |
| Memory grant feedback | Memory grant feedback adjusts the size of the memory allocated for a query based on past performance. SQL Server 2022 (16.x) introduces Percentile and Persistence mode memory grant feedback. Requires enabling Query Store. - Persistence: A capability that allows the memory grant feedback for a given cached plan to be persisted in the Query Store so that feedback can be reused after cache evictions. Persistence benefits memory grant feedback as well as the new DOP and CE feedback features. - Percentile: A new algorithm improves performance of queries with widely oscillating memory requirements, using memory grant information from several previous query executions over, instead of just the memory grant from the immediately preceding query execution. Requires enabling Query Store. Query Store is enabled by default for newly created databases as of SQL Server 2022 CTP 2.1. |
| Parameter sensitive plan optimization | Automatically enables multiple, active cached plans for a single parameterized statement. Cached execution plans accommodate largely different data sizes based on the customer-provided runtime parameter values. For more information, see Parameter Sensitive Plan optimization. |
| Degree of parallelism (DOP) feedback | A new database scoped configuration option DOP_FEEDBACK automatically adjusts degree of parallelism for repeating queries to optimize for workloads where inefficient parallelism can cause performance issues. Similar to optimizations in Azure SQL Database. Requires the Query Store to be enabled and in "Read write" mode.Beginning with RC 0, every query recompilation SQL Server compares the runtime stats of the query using existing feedback to the runtime stats of the previous compilation with the existing feedback. If the performance isn't the same or better, we clear all DOP feedback and trigger a reanalysis of the query starting from the compiled DOP. See Degree of parallelism (DOP) feedback. |
| Cardinality estimation feedback | Identifies and corrects suboptimal query execution plans for repeating queries, when these issues are caused by incorrect estimation model assumptions. Requires the Query Store to be enabled and in "Read write" mode. See Cardinality estimation (CE) feedback. |
| Optimized plan forcing | Uses compilation replay to improve the compilation time for forced plan generation by pre-caching non-repeatable plan compilation steps. Learn more in Optimized plan forcing with Query Store. |
Management
| New feature or update | Details |
|---|---|
| Integrated setup experience for the Azure extension for SQL Server | Install the Azure extension for SQL Server at setup. Required for Azure integration features. For more information, see: - Install SQL Server from the Command Prompt - Install SQL Server from the Installation Wizard (Setup). |
| Manage Azure extension for SQL Server | Use SQL Server Configuration Manager to manage Azure extension for SQL Server service. Required to create a SQL Server enabled by Azure Arc instance, and for other Azure connected features. See SQL Server Configuration Manager. |
| Max server memory calculations | During setup, SQL Setup recommends a value for max server memory to align with documented recommendations. The underlying calculation is different in SQL Server 2022 (16.x) to reflect recommended server memory configuration options. |
| Accelerated Database Recovery (ADR) improvements | There are several improvements to address persistent version store (PVS) storage and improve overall scalability. SQL Server 2022 (16.x) implements a persistent version store cleaner thread per database instead of per instance and the memory footprint for PVS page tracker has been improved. There are also several ADR efficiency improvements, such as concurrency improvements that help the cleanup process to work more efficiently. ADR cleans pages that couldn't previously be cleaned due to locking. See ADR improvements in SQL Server 2022 (16.x). |
| Improved snapshot backup support | Adds Transact-SQL support for freezing and thawing I/O without requiring a VDI client. Create a Transact-SQL snapshot backup. |
| Shrink database WAIT_AT_LOW_PRIORITY | In previous releases, shrinking databases and database files to reclaim space often leads to concurrency issues. SQL Server 2022 (16.x) adds WAIT_AT_LOW_PRIORITY as an additional option for shrink operations (DBCC SHRINKDATABASE and DBCC SHRINKFILE). When you specify WAIT_AT_LOW_PRIORITY, new queries requiring Sch-S or Sch-M locks aren't blocked by the waiting shrink operation, until the shrink operation stops waiting and begins executing. See Shrink a database and Shrink a file. |
| XML compression | XML compression provides a method to compress off-row XML data for both XML columns and indexes, improving capacity requirements. For more information, see CREATE TABLE and CREATE INDEX. |
| Asynchronous auto update statistics concurrency | Avoid potential concurrency issues using asynchronous statistics update if you enable the ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY database-scoped configuration. |
| Backup and restore to S3-compatible object storage | SQL Server 2022 (16.x) extends the BACKUP/RESTORE TO/FROM URL syntax by adding support for a new S3 connector using the REST API. See backup to URL. |
Platform
| New feature or update | Details |
|---|---|
| SQL Server Native Client (SNAC) has been removed | SQL Server Native Client (SNAC) isn't shipped with:
|
| Hybrid buffer pool with direct write | Reduces the number of memcpy commands that need to be performed on modified data or index pages residing on PMEM devices. This enlightenment is now available for Window 2022 and Linux. For details, see Hybrid buffer pool with direct write and Configure persistent memory (PMEM) for SQL Server on Windows. |
| Integrated acceleration & offloading | SQL Server 2022 (16.x) uses acceleration technologies from partners such as Intel to provide extended capabilities. At release, Intel® QuickAssist Technology (QAT) provides backup compression and hardware offloading. For more information, see Integrated acceleration and offloading. |
| Improved optimization | SQL Server 2022 (16.x) uses new hardware capabilities, including the Advanced Vector Extension (AVX) 512 extension to improve batch mode operations. Requires trace flag 15097. See DBCC TRACEON - Trace Flags (Transact-SQL). |
Language
| New feature or update | Details |
|---|---|
| Resumable add table constraints | Supports pausing and resuming an ALTER TABLE ADD CONSTRAINT operation. Resume such operation after maintenance windows, failovers, or system failures. |
| CREATE INDEX | WAIT_AT_LOW_PRIORITY with online index operations clause added. |
| Transactional replication | Peer-to-peer replication enables conflict detection and resolution to allow last writer to win. Originally introduced in SQL Server 2019 (15.x) CU 13. For more information, see Automatically handle conflicts with last write wins. |
| CREATE STATISTICS | Adds AUTO_DROP option Automatic statistics with low priority. |
| SELECT ... WINDOW clause | Determines the partitioning and ordering of a rowset before the window function, which uses the window in OVER clause is applied. See SELECT - WINDOW. |
| IS [NOT] DISTINCT FROM | Determines whether two expressions when compared with each other evaluate to NULL, and guarantees a true or false value as the result. For more information, see IS [NOT] DISTINCT FROM (Transact-SQL). |
| Time series functions | You can store and analyze data that changes over time, using time-windowing, aggregation, and filtering capabilities. - DATE_BUCKET - GENERATE_SERIES The following functions add support to IGNORE NULLS and RESPECT NULLS:- FIRST_VALUE - LAST_VALUE |
| JSON functions | - ISJSON - JSON_PATH_EXISTS - JSON_OBJECT - JSON_ARRAY |
| Aggregate functions | - APPROX_PERCENTILE_CONT - APPROX_PERCENTILE_DISC |
| T-SQL functions | - Logical functions - GREATEST - Logical functions - LEAST - STRING_SPLIT - DATETRUNC - LTRIM - RTRIM - TRIM |
| Bit manipulation functions | - LEFT_SHIFT (Transact SQL) - RIGHT_SHIFT (Transact SQL) - BIT_COUNT (Transact SQL) - GET_BIT (Transact SQL) - SET_BIT (Transact SQL) |
Tools
| New feature or update | Details |
|---|---|
| Azure Data Studio | Get the latest release at Download and install Azure Data Studio. The latest release includes support for SQL Server 2022 (16.x). |
| Distributed Replay | SQL Server setup no longer includes the Distributed Replay client and controller executables. These are available, along with the Admin executable, as a separate download |
| SQL Server Management Studio | SSMS version 19.0 is now available and is the recommended version of SSMS for SQL Server 2022 (16.x). Download SQL Server Management Studio (SSMS). |
| SqlPackage.exe | Version 19 of SqlPackage provides support for SQL Server 2022 (16.x). Get the latest version at Download and install SqlPackage. |
| VS Code | Version 1.67 of VS Code and higher support SQL Server 2022 (16.x). Get the latest release at https://code.visualstudio.com/. |
SQL Machine Learning Services
Beginning with SQL Server 2022 (16.x), runtimes for R, Python, and Java, are no longer installed with SQL Setup. Instead, install any desired custom runtimes and packages. For more information, see Install SQL Server 2022 Machine Learning Services (Python and R) on Windows or Install SQL Server 2022 Machine Learning Services (Python and R) on Linux.
Additional information
This section provides additional information for the features highlighted previously.
Query Store improvements
Query Store helps you better track performance history, troubleshoot query plan related issues, and enable new capabilities in Azure SQL Database, Azure SQL Managed Instance, and SQL Server 2022 (16.x). CTP 2.1 introduces Query Store enabled by default for new databases. If you need to enable the Query Store, see Enable the Query Store.
For databases that have been restored from other SQL Server instances and for those databases that are upgraded from an in-place upgrade to SQL Server 2022 (16.x), these databases retain the previous Query Store settings.
For databases that are restored from previous SQL Server instances, separately evaluate the database compatibility level settings as some Intelligent Query Processing features are enabled by the compatibility level setting.
If there's concern about the overhead Query Store might introduce, administrators can use custom capture policies to further tune what the Query Store captures. Custom capture policies are available to help further tune Query Store captures. Custom capture policies can be used to be more selective about which queries, and query details are captured. For example, an administrator might choose to capture only the most expensive queries, repeated queries, or the queries that have a high level of compute overhead. Custom capture policies can help Query Store capture the most important queries in your workload. Except for the STALE_CAPTURE_POLICY_THRESHOLD option, these options define the OR conditions that need to happen for queries to be captured in the defined Stale Capture Policy Threshold value. For example, these are the default values in the QUERY_CAPTURE_MODE = AUTO:
...
QUERY_CAPTURE_MODE = CUSTOM,
QUERY_CAPTURE_POLICY = (
STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
EXECUTION_COUNT = 30,
TOTAL_COMPILE_CPU_TIME_MS = 1000,
TOTAL_EXECUTION_CPU_TIME_MS = 100
)
...
SQL Server service is set to Automatic (Delayed Start) start mode
In SQL Server 2022 (16.x) and later versions, when you set the Start Mode for a SQL Server service to Automatic in Configuration Manager, the service starts in Automatic (Delayed Start) mode instead, even though the Start Mode shows as Automatic.
SQL Server Analysis Services
This release introduces new features and improvements for performance, resource governance, and client support. For specific updates, see What's new in SQL Server Analysis Services.
SQL Server Reporting Services
This release introduces new features and improvements for accessibility, security, reliability, and bug fixes. For specific updates, see What's new in SQL Server Reporting Services (SSRS).
SQL Server Integration Services
For changes related to SQL Server Integration Services, see Integration Services Backward Compatibility.
Other services
This release doesn't add features for SQL Server Master Data Services.
Related content
- SqlServer PowerShell module
- SQL Server PowerShell
- SQL Server Workshops
- SQL Server 2022 release notes
Get help
- Ideas for SQL: Have suggestions for improving SQL Server?
- Microsoft Q & A (SQL Server)
- DBA Stack Exchange (tag sql-server): Ask SQL Server questions
- Stack Overflow (tag sql-server): Answers to SQL development questions
- Reddit: General discussion about SQL Server
- Microsoft SQL Server License Terms and Information
- Support options for business users
- Contact Microsoft
- Additional SQL Server help and feedback
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