Applies to: Sql Server 2022 (16.x)
SQL Server 2022 represents a significant evolution of the SQL Server platform, building upon its well-established foundations to offer users even greater flexibility and power. This latest iteration continues the tradition of providing diverse options for development languages, varied data types, adaptable deployment environments (on-premises or cloud), and broad operating system compatibility. SQL Server 2022 is designed to meet the evolving needs of modern data estates, providing enhanced performance, security, and availability.
To get a quick visual overview of the key highlights in SQL Server 2022, watch the introductory video below:
For more in-depth video content and insights, you can also explore:
This article provides a comprehensive summary of the new features and enhancements introduced in SQL Server 2022 (16.x), offering a detailed look at what this version brings to the table.
Get SQL Server 2022 (16.x)
Ready to experience the new features? Download the SQL Server 2022 Evaluation Edition here. The current build number is 16.0.1000.6.
For detailed information regarding this release, including important known issues, please refer to the SQL Server 2022 release notes.
To ensure you have the optimal experience when working with SQL Server 2022 (16.x), it’s highly recommended to utilize the latest tools available.
Feature Highlights of SQL Server 2022
SQL Server 2022 is packed with new features and significant improvements designed to boost performance, enhance security, and provide more robust management capabilities. The following sections highlight the key features that have been either improved or newly introduced in SQL Server 2022 (16.x).
Analytics Enhancements in SQL Server 2022
| New Feature or Update | Details |
|---|---|
| Azure Synapse Link for SQL Server 2022 | SQL Server 2022 introduces Azure Synapse Link for SQL, enabling near real-time analytics over your operational data. This feature provides a seamless bridge between operational stores within SQL Server 2022 and Azure Synapse Analytics dedicated SQL pools. Azure Synapse Link for SQL empowers you to perform analytics, business intelligence, and machine learning tasks directly on your operational data with minimal impact on the source databases. This is achieved through a novel change feed technology that efficiently captures and transmits data changes. For more comprehensive information, see What is Azure Synapse Link for SQL?. It’s also important to be aware of the Limitations and known issues with Azure Synapse Link for SQL. |
| Object Storage Integration for SQL Server 2022 | SQL Server 2022 expands its data platform integration with new object storage capabilities. Beyond Azure Storage, SQL Server 2022 now integrates with S3-compatible object storage. This integration manifests in two key features: first, backup to URL functionality, allowing direct backups to S3-compatible storage, and second, Data Lake Virtualization. Data Lake Virtualization enhances PolyBase with S3-compatible object storage, and introduces support for querying Parquet files using T-SQL, broadening the scope of data analysis directly within SQL Server 2022. |
| Data Virtualization Improvements in SQL Server 2022 | SQL Server 2022 strengthens data virtualization by enabling querying of diverse data types across various data sources directly from SQL Server. With SQL Server 2022 Cumulative Update 2, support is now included for Oracle TNS files, further expanding connectivity options. For detailed information and practical examples, refer to CREATE EXTERNAL DATA SOURCE (Transact-SQL) CONNECTION_OPTIONS. |
Availability Enhancements in SQL Server 2022
| New Feature or Update | Details |
|---|---|
| Link to Azure SQL Managed Instance in SQL Server 2022 | SQL Server 2022 introduces the capability to establish a link to Azure SQL Managed Instance, facilitating seamless data replication between your on-premises SQL Server instance and Azure SQL Managed Instance. This link is designed to support both disaster recovery scenarios and migration strategies to the cloud. For a comprehensive understanding, see Overview of the Managed Instance link. |
| Contained Availability Groups in SQL Server 2022 | SQL Server 2022 introduces Contained Availability Groups, a new type of Always On Availability Group with enhanced isolation and management. These availability groups offer: – Independent metadata object management: Managing users, logins, permissions, SQL Agent jobs, and more at the availability group level, separate from the instance level. – Inclusion of contained system databases: Incorporating specialized contained system databases within the availability group for a more self-contained environment. To learn more, see What is a contained availability group? |
| Distributed Availability Group Improvements in SQL Server 2022 | Distributed Availability Groups in SQL Server 2022 are enhanced to utilize multiple TCP connections. This improvement is specifically aimed at optimizing network bandwidth utilization across remote links that experience long TCP latencies, leading to more efficient data replication and synchronization. |
| Improved Backup Metadata in SQL Server 2022 | SQL Server 2022 enhances backup metadata by updating the backupset system table to return the last valid restore time. This provides more readily accessible information for backup history and recovery planning. See backupset for more details. |
Security Enhancements in SQL Server 2022
| New Feature or Update | Details |
|---|---|
| Microsoft Defender for Cloud Integration with SQL Server 2022 | SQL Server 2022 integrates with Microsoft Defender for Cloud to provide robust protection for your SQL servers through the Defender for SQL plan. This integration requires the SQL Server Extension for Azure to be enabled and offers functionalities for discovering and mitigating potential database vulnerabilities. It also detects anomalous activities that could indicate threats to your databases. For a comprehensive overview of how Defender for SQL enhances your database security posture, see Overview of Microsoft Defender for Azure SQL. |
| Microsoft Purview Integration with SQL Server 2022 | SQL Server 2022 extends its integration with Microsoft Purview, allowing you to apply Microsoft Purview access policies to any SQL Server instance enrolled in both Azure Arc and Microsoft Purview Data Use Management. New SQL Performance Monitor and SQL Security Auditor roles are introduced, aligning with the principle of least privilege through Microsoft Purview access policies. For detailed setup information, see Provision access by data owner for SQL Server enabled by Azure Arc. |
| Ledger Feature in SQL Server 2022 | The Ledger feature in SQL Server 2022 provides tamper-evidence capabilities directly within your database. This innovative security feature allows you to cryptographically verify to auditors and other business stakeholders that your data has not been tampered with or altered without detection. Explore Ledger overview for more information. |
| Microsoft Entra Authentication Enhancements in SQL Server 2022 | SQL Server 2022 enhances Microsoft Entra ID (formerly Azure Active Directory) authentication capabilities. You can now leverage authentication with Microsoft Entra ID to connect to SQL Server. Starting with SQL Server 2022 Cumulative Update 6, transactional replication can be configured with Microsoft Entra authentication, a feature that reached general availability (GA) in Cumulative Update 12. See configure transactional replication with Microsoft Entra authentication for implementation details. |
| Always Encrypted with Secure Enclaves Improvements in SQL Server 2022 | SQL Server 2022 significantly improves Always Encrypted with secure enclaves by adding support for JOIN, GROUP BY, and ORDER BY operations, as well as text columns using UTF-8 collations within confidential queries. Performance has also been enhanced, making secure enclaves more practical for a wider range of applications. Learn more about Always Encrypted with secure enclaves. |
| Granular Permissions in SQL Server 2022 Access Control | SQL Server 2022 introduces new granular permissions to improve adherence to the Principle of Least Privilege. These permissions provide finer control over access rights, enhancing security and compliance. For an in-depth explanation of the revamped SQL Permission system, see revamped SQL Permission system for Principle of Least Privilege and external policies. |
| Server-Level Roles in SQL Server 2022 Access Control | New built-in server-level roles in SQL Server 2022 enable least privileged access for administrative tasks that span the entire SQL Server Instance. These roles simplify permission management and enhance security by limiting unnecessary broad administrative rights. |
| Granular UNMASK Permissions for Dynamic Data Masking in SQL Server 2022 | SQL Server 2022 introduces granular UNMASK permissions for Dynamic Data Masking. This enhancement provides more precise control over who can view unmasked data, further improving data privacy and security. |
| PFX Certificate Support and Cryptographic Improvements in SQL Server 2022 | SQL Server 2022 adds support for importing and exporting certificates and private keys in PFX file format certificates. It also enables backing up backup and restore of master keys to Azure Blob Storage. SQL Server-generated certificates now feature a default RSA key size of 3,072 bits, increasing cryptographic strength. New T-SQL commands BACKUP SYMMETRIC KEY and RESTORE SYMMETRIC KEY are also introduced. See also, BACKUP CERTIFICATE. |
| MS-TDS 8.0 Protocol Support in SQL Server 2022 | SQL Server 2022 implements the new MS-TDS protocol iteration, version 8.0. See TDS 8.0 support and TLS 1.3 support for details. Key improvements include: – Mandatory encryption for all connections. – Alignment of MS-TDS with HTTPS, enhancing manageability by network appliances for improved security. – Removal of MS-TDS/TLS custom interleaving, enabling the use of TLS 1.3 and subsequent TLS protocol versions, offering state-of-the-art encryption and security standards. |
Performance Enhancements in SQL Server 2022
| New Feature or Update | Details |
|---|---|
| System Page Latch Concurrency Enhancements in SQL Server 2022 | SQL Server 2022 improves system page latch concurrency by optimizing concurrent updates to global allocation map (GAM) pages and shared global allocation map (SGAM) pages. This reduces page latch contention during allocation and deallocation of data pages and extents. These enhancements are universally beneficial across all user databases and are particularly advantageous for tempdb intensive workloads. |
| Buffer Pool Parallel Scan in SQL Server 2022 | SQL Server 2022 enhances the performance of buffer pool scan operations, especially on large-memory machines, by leveraging multiple CPU cores for parallel scanning. This drastically reduces the time for buffer pool scans on systems with substantial memory. Learn more about Operations that trigger a buffer pool scan might run slowly on large-memory computers. |
| Ordered Clustered Columnstore Index in SQL Server 2022 | SQL Server 2022 introduces the ordered clustered columnstore index (CCI). This type of index sorts existing data in memory before the index builder compresses the data into index segments. This pre-sorting has the potential to significantly improve segment elimination efficiency, leading to enhanced query performance by reducing the number of segments that need to be read from disk. For more information, see CREATE COLUMNSTORE INDEX and What’s new in columnstore indexes. For performance tuning guidance, see Performance tuning with ordered columnstore indexes. |
| Improved Columnstore Segment Elimination in SQL Server 2022 | SQL Server 2022 enhances segment elimination for all columnstore indexes by data type. Data type choices can significantly affect query performance based on common filter predicates in queries against columnstore indexes. Previously, segment elimination was applied to numeric, date, time data types, and datetimeoffset with a scale of two or less. SQL Server 2022 extends these capabilities to string, binary, GUID data types, and datetimeoffset data type for scales greater than two, vastly improving query efficiency across a broader range of data types. |
| In-Memory OLTP Management Improvements in SQL Server 2022 | SQL Server 2022 provides improved memory management for In-Memory OLTP in large memory servers. These enhancements are designed to reduce out-of-memory conditions and ensure more stable and predictable performance for In-Memory OLTP workloads under high memory pressure. |
| Virtual Log File Growth Optimizations in SQL Server 2022 | SQL Server 2022 modifies the virtual log file (VLF) growth behavior. In prior versions, a growth increment exceeding 1/8 of the current log size and less than 64 MB resulted in the creation of four VLFs. In SQL Server 2022, if the growth is less than or equal to 64 MB and more than 1/8 of the current log size, only one VLF is created. This change aims to optimize VLF counts, reducing potential performance issues associated with excessively fragmented transaction logs. For more details on VLF growth, see Virtual Log Files (VLFs). |
| Thread Management Enhancements in SQL Server 2022 | SQL Server 2022 introduces several thread management improvements: – ParallelRedoThreadPool: An instance-level thread pool shared across all databases for redo operations. This allows each database to benefit from parallel redo, enabling multiple threads to process log records of a single database concurrently. Previously, the number of redo threads was capped at 100. – ParallelRedoBatchRedo: Batching of log record redo operations under a single latch to improve speed. This enhancement accelerates recovery, catchup redo, and crash recovery redo processes. |
| Reduced Buffer Pool I/O Promotions in SQL Server 2022 | SQL Server 2022 reduces the frequency of single-page promotions to eight-page extents when populating the buffer pool from storage. This minimizes unnecessary I/O operations. The buffer pool population becomes more efficient thanks to improvements in the read-ahead mechanism, leading to reduced I/O overhead and faster data access. This change is included in all editions of SQL Server 2022, as well as Azure SQL Database and Azure SQL Managed Instance. |
| Enhanced Spinlock Algorithms in SQL Server 2022 | SQL Server 2022 features internal adjustments to the Database Engine that enhance the efficiency of spinlocks, which are critical for maintaining consistency among multiple threads. These algorithmic improvements make spinlocks more efficient, reducing contention and improving overall concurrency. This enhancement is available in all SQL Server 2022 editions, Azure SQL Database, and Azure SQL Managed Instance. |
| Improved Virtual Log File (VLF) Algorithms in SQL Server 2022 | SQL Server 2022 changes the algorithm for creating virtual log files (VLFs) during transaction log growth scenarios. Having a large number of small VLFs due to frequent log growth can negatively impact performance, especially for recovery operations. The new algorithm aims to create a more optimal number of VLFs, balancing space management and performance. To understand the changes in SQL Server 2022 (16.x), see Virtual Log Files (VLFs). This improvement is implemented across all editions of SQL Server 2022 and Azure SQL Database. |
| Instant File Initialization for Transaction Log Growth Events in SQL Server 2022 | SQL Server 2022 extends Instant File Initialization (IFI) benefits to transaction log growth events up to 64 MB. While transaction log files generally cannot fully utilize IFI, SQL Server 2022 allows IFI to expedite transaction log file autogrowth events up to 64 MB. Given that the default autogrowth size for new databases is 64 MB, many transaction log autogrowth operations can now benefit from IFI. Autogrowth events larger than 64 MB will not benefit from instant file initialization. For more details, see Database instant file initialization. |
Query Store and Intelligent Query Processing in SQL Server 2022
The intelligent query processing (IQP) feature family in SQL Server 2022 includes features designed to automatically improve the performance of existing workloads with minimal to no implementation effort. These features intelligently optimize query execution based on runtime conditions and historical data.
Diagram of the Intelligent Query Processing family of features and when they were first introduced to SQL Server.
| New Feature or Update | Details |
|---|---|
| Query Store on Secondary Replicas in SQL Server 2022 | SQL Server 2022 extends the Query Store functionality to secondary replicas in Always On Availability Groups. This enhancement, known as Query Store on secondary replicas, enables the same Query Store capabilities for workloads running on secondary replicas as are available on primary replicas. This provides consistent performance monitoring and troubleshooting across all replicas. Learn more in Query Store for secondary replicas. For further information, see Query Store improvements later in this article. |
| Query Store Hints in SQL Server 2022 | SQL Server 2022 introduces Query Store hints. Query Store hints leverage the Query Store to provide a method for shaping query plans without requiring changes to application code. Previously available only in Azure SQL Database and Azure SQL Managed Instance, this powerful feature is now available in SQL Server 2022 (16.x), provided that Query Store is enabled and in “Read write” mode. |
| Memory Grant Feedback Enhancements in SQL Server 2022 | Memory grant feedback, which dynamically adjusts the memory allocated for a query based on past performance, is enhanced in SQL Server 2022 (16.x) with Percentile and Persistence mode memory grant feedback. These improvements require Query Store to be enabled. – Persistence: This new capability allows memory grant feedback for a cached plan to be stored in the Query Store, ensuring that feedback can be reused even after cache evictions. Persistence benefits both memory grant feedback and the new DOP and CE feedback features. – Percentile: A new algorithm that improves the performance of queries with fluctuating memory requirements. It uses memory grant information from multiple past executions instead of just the immediately preceding execution, leading to more stable and effective memory allocation. Requires Query Store enablement, which is the default for newly created databases in SQL Server 2022 CTP 2.1 and later. |
| Parameter Sensitive Plan Optimization in SQL Server 2022 | SQL Server 2022 introduces Parameter Sensitive Plan optimization. This feature automatically enables multiple, active cached plans for a single parameterized statement. This is crucial for accommodating queries where runtime parameter values provided by users result in vastly different data sizes, ensuring optimal plan selection for varying parameter inputs. For more information, see Parameter Sensitive Plan optimization. |
| Degree of Parallelism (DOP) Feedback in SQL Server 2022 | SQL Server 2022 includes Degree of Parallelism (DOP) feedback. A new database scoped configuration option, DOP_FEEDBACK, automatically adjusts the degree of parallelism for repeating queries. This optimization targets workloads where inefficient parallelism can cause performance bottlenecks. This functionality is similar to optimizations already present in Azure SQL Database. It requires Query Store to be enabled and in “Read write” mode. Starting with Release Candidate 0 (RC 0), SQL Server compares runtime statistics of a query using existing feedback against the runtime stats of the previous compilation with existing feedback at every query recompilation. If performance degrades, all DOP feedback is cleared, and a reanalysis of the query begins, starting from the compiled DOP. See Degree of parallelism (DOP) feedback. |
| Cardinality Estimation (CE) Feedback in SQL Server 2022 | SQL Server 2022 incorporates Cardinality Estimation (CE) feedback. This intelligent feature identifies and corrects suboptimal query execution plans for recurring queries when these issues are caused by incorrect assumptions in the cardinality estimation model. It requires Query Store to be enabled and in “Read write” mode. See Cardinality estimation (CE) feedback for more details. |
| Optimized Plan Forcing in SQL Server 2022 | SQL Server 2022 introduces Optimized Plan Forcing. This feature utilizes compilation replay to enhance the compilation time for forced plan generation by pre-caching non-repeatable plan compilation steps. This significantly speeds up the process of forcing query plans using Query Store. Learn more in Optimized plan forcing with Query Store. |
Management Enhancements in SQL Server 2022
| New Feature or Update | Details |
|---|---|
| Integrated Setup Experience for Azure Extension for SQL Server in SQL Server 2022 | SQL Server 2022 streamlines the setup process by integrating the installation of the Azure extension for SQL Server directly into the setup workflow. This extension is essential for Azure integration features. For detailed instructions, see: – Install SQL Server from the Command Prompt – Install SQL Server from the Installation Wizard (Setup). |
| Manage Azure Extension for SQL Server in SQL Server 2022 | SQL Server 2022 allows you to manage the Azure extension for SQL Server service using SQL Server Configuration Manager. This management capability is required for creating SQL Server instances enabled by Azure Arc and for utilizing other Azure-connected features effectively. See SQL Server Configuration Manager for more details. |
| Max Server Memory Calculations in SQL Server 2022 Setup | During setup, SQL Server 2022 now provides a more refined recommendation for max server memory. SQL Setup suggests a value that aligns with documented best practices and recommendations for server memory configuration. The underlying calculation has been updated in SQL Server 2022 (16.x) to better reflect optimal server memory configuration options. |
| Accelerated Database Recovery (ADR) Improvements in SQL Server 2022 | SQL Server 2022 includes several improvements to Accelerated Database Recovery (ADR) focused on persistent version store (PVS) storage and overall scalability. These enhancements include: – A multithreaded persistent version store cleaner for more efficient cleanup operations. – Reduced memory footprint for the PVS page tracker. – General ADR efficiency improvements, including concurrency enhancements that enable the cleanup process to work more effectively and clean pages that were previously un-cleanable due to locking issues. See ADR improvements in SQL Server 2022 (16.x) for comprehensive details. |
| Improved Snapshot Backup Support in SQL Server 2022 | SQL Server 2022 adds Transact-SQL support for freezing and thawing I/O operations for snapshot backups. This eliminates the need for a VDI client, simplifying the process of creating snapshot backups. Create a Transact-SQL snapshot backup. |
| Shrink Database WAIT_AT_LOW_PRIORITY Option in SQL Server 2022 | SQL Server 2022 addresses concurrency issues often encountered during database and database file shrinking operations. It introduces WAIT_AT_LOW_PRIORITY as an additional option for shrink operations (DBCC SHRINKDATABASE and DBCC SHRINKFILE). When specified, new queries requiring Sch-S or Sch-M locks are not blocked by the shrink operation while it is waiting, improving concurrency and availability. See Shrink a database and Shrink a file for usage details. |
| XML Compression in SQL Server 2022 | SQL Server 2022 introduces XML compression, providing a method to compress off-row XML data for both XML columns and XML indexes. This feature significantly reduces storage capacity requirements for databases with substantial XML data. For more information, see CREATE TABLE and CREATE INDEX. |
| Asynchronous Auto Update Statistics Concurrency Improvement in SQL Server 2022 | SQL Server 2022 helps avoid potential concurrency issues associated with asynchronous statistics updates. By enabling the ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY database-scoped configuration, you can prioritize query execution over asynchronous statistics updates, reducing blocking and improving overall system responsiveness. |
| Backup and Restore to S3-Compatible Object Storage in SQL Server 2022 | SQL Server 2022 expands the BACKUP/RESTORE TO/FROM URL syntax to include support for S3-compatible object storage. This is achieved through a new S3 connector that utilizes the REST API, providing more flexible backup and restore options. See backup to URL for syntax and usage. |
Platform Enhancements in SQL Server 2022
| New Feature or Update | Details |
|---|---|
| SQL Server Native Client (SNAC) Removal in SQL Server 2022 | SQL Server Native Client (SNAC) is no longer included with SQL Server 2022 (16.x) and later versions, nor with SQL Server Management Studio 19 and later. SQL Server Native Client (SQLNCLI or SQLNCLI11) and the legacy Microsoft OLE DB Provider for SQL Server (SQLOLEDB) are not recommended for new application development. For new projects, it is recommended to use one of the following drivers. For SQLNCLI that ships as a component of SQL Server Database Engine (versions 2012 through 2019), see this Support Lifecycle exception. |
| Hybrid Buffer Pool with Direct Write in SQL Server 2022 | SQL Server 2022 introduces a hybrid buffer pool with direct write capability. This feature reduces the number of memcpy commands required for modified data or index pages residing on Persistent Memory (PMEM) devices. This optimization is now available for both Windows 2022 and Linux, enhancing performance when using PMEM. For details, see Hybrid buffer pool with direct write and Configure persistent memory (PMEM) for SQL Server on Windows. |
| Integrated Acceleration & Offloading in SQL Server 2022 | SQL Server 2022 leverages acceleration technologies from partners like Intel to provide extended capabilities. At release, Intel® QuickAssist Technology (QAT) is integrated to offer hardware offloading for backup compression. This integration improves performance and reduces CPU load for backup operations. For more information, see Integrated acceleration and offloading. |
| Improved Optimization with Advanced Vector Extensions (AVX) 512 in SQL Server 2022 | SQL Server 2022 (16.x) utilizes new hardware capabilities, specifically Advanced Vector Extensions (AVX) 512, to enhance batch mode operations. This optimization improves performance for workloads that can benefit from vectorized processing. To enable this feature, trace flag 15097 is required. See DBCC TRACEON – Trace Flags (Transact-SQL). |
Language Enhancements in SQL Server 2022
| New Feature or Update | Details |
|---|---|
| Resumable Add Table Constraints in SQL Server 2022 | SQL Server 2022 supports pausing and resuming ALTER TABLE ADD CONSTRAINT operations. This allows you to resume such operations after maintenance windows, failovers, or system failures, providing greater control and flexibility in managing schema changes. |
| CREATE INDEX WAIT_AT_LOW_PRIORITY Option in SQL Server 2022 | The WAIT_AT_LOW_PRIORITY clause has been added to online index operations in SQL Server 2022. This option allows you to minimize the impact of online index creation operations on concurrent workloads by specifying that the index operation should wait for low priority locks. |
| Transactional Replication Conflict Detection and Resolution Improvements in SQL Server 2022 | Transactional replication in SQL Server 2022, specifically peer-to-peer replication, now includes conflict detection and resolution with a “last writer wins” policy. Originally introduced in SQL Server 2019 (15.x) CU 13, this feature is enhanced in SQL Server 2022 to automatically handle conflicts in peer-to-peer replication environments based on timestamp. For more information, see Automatically handle conflicts with last write wins. |
| CREATE STATISTICS AUTO_DROP Option in SQL Server 2022 | SQL Server 2022 adds the AUTO_DROP option to CREATE STATISTICS. This feature enables automatic statistics creation with low priority, reducing the resource contention of statistics creation operations. |
| SELECT … WINDOW Clause in SQL Server 2022 | SQL Server 2022 introduces the SELECT – WINDOW clause. This clause determines the partitioning and ordering of a rowset before a window function, which uses the window in the OVER clause, is applied. It simplifies and clarifies window function syntax. |
| IS [NOT] DISTINCT FROM Operator in SQL Server 2022 | SQL Server 2022 includes the IS [NOT] DISTINCT FROM (Transact-SQL) operator. This operator compares two expressions and reliably determines if they are distinct, even when dealing with NULL values, guaranteeing a true or false result. |
| Time Series Functions in SQL Server 2022 | SQL Server 2022 enhances time series data analysis capabilities with new functions: – DATE_BUCKET: Groups date or time values into buckets of a specified size. – GENERATE_SERIES: Generates a series of values within a specified interval. The following functions now support IGNORE NULLS and RESPECT NULLS clauses: – FIRST_VALUE – LAST_VALUE |
| JSON Functions in SQL Server 2022 | SQL Server 2022 introduces several new JSON functions to enhance JSON data handling: – ISJSON: Tests if a string is valid JSON. – JSON_PATH_EXISTS: Checks if a path exists in JSON text. – JSON_OBJECT: Creates a JSON object from input values. – JSON_ARRAY: Creates a JSON array from input values. |
| Aggregate Functions in SQL Server 2022 | SQL Server 2022 adds new aggregate functions for statistical analysis: – APPROX_PERCENTILE_CONT: Returns the approximate percentile based on continuous distribution. – APPROX_PERCENTILE_DISC: Returns the approximate percentile based on discrete distribution. |
| T-SQL Functions in SQL Server 2022 | SQL Server 2022 expands T-SQL with a range of new functions: – Logical functions – GREATEST: Returns the largest value from a list of expressions. – Logical functions – LEAST: Returns the smallest value from a list of expressions. – STRING_SPLIT: Splits a string into substrings based on a separator. – DATETRUNC: Truncates a date or time value to the specified precision. – LTRIM: Removes leading spaces from a string. – RTRIM: Removes trailing spaces from a string. – TRIM: Removes leading and trailing spaces from a string. |
| Bit Manipulation Functions in SQL Server 2022 | SQL Server 2022 introduces a suite of Bit manipulation functions for bitwise operations: – LEFT_SHIFT (Transact SQL): Shifts bits to the left. – RIGHT_SHIFT (Transact SQL): Shifts bits to the right. – BIT_COUNT (Transact SQL): Counts the number of set bits in an integer. – GET_BIT (Transact SQL): Gets the value of a specific bit. – SET_BIT (Transact SQL): Sets the value of a specific bit. |
Tools Enhancements in SQL Server 2022
| New Feature or Update | Details |
|---|---|
| Azure Data Studio Support for SQL Server 2022 | Azure Data Studio offers full support for SQL Server 2022 (16.x). Get the latest release at Download and install Azure Data Studio. The latest version ensures compatibility and takes advantage of the new features in SQL Server 2022. |
| Distributed Replay Availability for SQL Server 2022 | For SQL Server 2022, the Distributed Replay client and controller executables are no longer included in the main SQL Server setup. They, along with the Admin executable, are available as a separate download, allowing for more tailored installations. |
| SQL Server Management Studio (SSMS) Support for SQL Server 2022 | SSMS version 19.0 and later is the recommended version of SSMS for managing SQL Server 2022 (16.x). Download SQL Server Management Studio (SSMS) to ensure you have the latest tools and capabilities for managing your SQL Server 2022 instances. |
| SqlPackage.exe Support for SQL Server 2022 | SqlPackage version 19 and later provides full support for SQL Server 2022 (16.x). Download the latest version at Download and install SqlPackage to utilize the latest features when working with SQL Server 2022 database deployments and management tasks. |
| VS Code Support for SQL Server 2022 | VS Code version 1.67 and higher offers support for SQL Server 2022 (16.x) through extensions. Get the latest release at https://code.visualstudio.com/. Using the latest VS Code ensures you can effectively develop and manage SQL Server 2022 databases. |
SQL Machine Learning Services in SQL Server 2022
Starting with SQL Server 2022 (16.x), the runtimes for R, Python, and Java are no longer automatically installed with SQL Setup. Instead, you have the flexibility to install any desired custom runtimes and packages separately. This change provides more control over the machine learning environment. For more information on installation, see: – Install SQL Server 2022 Machine Learning Services (Python and R) on Windows – Install SQL Server 2022 Machine Learning Services (Python and R) on Linux.
Additional Information on SQL Server 2022 Features
This section provides further details and context for some of the key features highlighted earlier in the SQL Server 2022 overview.
Query Store Improvements in SQL Server 2022
Query Store continues to be a crucial tool in SQL Server 2022, helping you track performance history, troubleshoot query plan issues, and enabling new capabilities. Starting with CTP 2.1, Query Store is enabled by default for newly created databases in SQL Server 2022. If you need to manually enable Query Store, see Enable the Query Store.
- For databases restored from other SQL Server instances or upgraded in-place to SQL Server 2022 (16.x), existing Query Store settings are preserved.
- For restored databases, it’s recommended to review the database compatibility level settings as several Intelligent Query Processing features are governed by the compatibility level.
To manage potential overhead from Query Store, administrators can utilize custom capture policies to fine-tune what Query Store captures. Custom policies allow selective capture of queries and their details, focusing on the most important queries in your workload, such as expensive, repeated, or high-compute overhead queries. Custom capture policies offer options beyond the STALE_CAPTURE_POLICY_THRESHOLD, defining OR conditions for query capture. For example, the default values in QUERY_CAPTURE_MODE = AUTO are:
... 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 Automatic (Delayed Start) Mode in SQL Server 2022
In SQL Server 2022 (16.x) and later, setting the Start Mode for a SQL Server service to Automatic in Configuration Manager now results in the service starting in Automatic (Delayed Start) mode. Although the Start Mode display still shows Automatic, the underlying behavior is Automatic (Delayed Start), optimizing system startup performance by delaying SQL Server service initialization.
SQL Server Analysis Services in SQL Server 2022
SQL Server 2022 Analysis Services introduces new features and improvements focused on performance, resource governance, and client support. For specific details on these updates, see What’s new in SQL Server Analysis Services.
SQL Server Reporting Services (SSRS) in SQL Server 2022
This release of SQL Server Reporting Services includes enhancements for accessibility, security, reliability, and various bug fixes. For a detailed list of updates, see What’s new in SQL Server Reporting Services (SSRS).
SQL Server Integration Services (SSIS) in SQL Server 2022
For information regarding changes and backward compatibility considerations for SQL Server Integration Services, see Integration Services Backward Compatibility.
Other Services in SQL Server 2022
This release of SQL Server 2022 does not introduce new features for SQL Server Master Data Services.
Related Content
Explore more about SQL Server 2022 and its features through these related resources:
Get Help with SQL Server 2022
Need assistance or have questions about SQL Server 2022? Here are resources to help:
Contribute to SQL Documentation
Did you know you can contribute to the SQL documentation? By editing SQL content, you not only help improve the documentation for everyone but also get credited as a contributor to the page.
For more information on how to contribute, see Edit Microsoft Learn documentation.
