What's new in 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.

https://learn.microsoft.com/en-us/sql/sql-server/what-s-new-in-sql-server-2022?view=sql-server-ver16

Feature highlights

Administrative:

Azure Synapse Link for SQL:  near real-time analytics With integration between SQL Server 2022 (16.x) and Azure Synapse Analytics dedicated SQL pools, Azure Synapse Link for SQL enables you to run analytics scenarios on your operational data with minimum impact on source databases.

Object storage integration:  introduces new object storage integration with S3-compatible object storage, in addition to Azure Storage. The first is backup to URL and the second is Data Lake Virtualization.

Link to Azure SQL Managed Instance: Connect your SQL Server instance to Azure SQL Managed Instance.

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.

Distributed availability group: - Now using multiple TCP connections for better network bandwidth utilization across a remote link with long tcp latencies.

Improved backup metadata: system table returns last valid restore time.

Azure Active Directory authentication: Use Azure Active Directory (Azure AD) authentication to connect to SQL Server.

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.

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. 

Manage Azure extension for SQL Server:Use SQL Server Configuration Manager to manage Azure extension for SQL Server service. 

Shrink database WAIT_AT_LOW_PRIORITY:In previous versions, shrinking databases and database files to reclaim space often leads to concurrency issues. SQL Server 2022 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

Developers:

CREATE INDEX

WAIT_AT_LOW_PRIORITY with online index operations clause added.

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 adds 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	- GREATEST () - LEAST () - STRING_SPLIT () - DATETRUNC () - LTRIM () - RTRIM () - TRIM ()
Bit manipulation functions	- LEFT_SHIFT () - RIGHT_SHIFT () - BIT_COUNT () - GET_BIT () - SET_BIT ()