What's New in MS SQL Server 2022

Microsoft SQL Server 2022 is almost here! In this article, we’ll review what new enhancements and features will be coming to this popular relational database.

The new version of MS SQL Server will become available almost three years after SQL Server 2019 was released. (You can learn more about the origins and different versions of SQL Server in A Brief History of MS SQL Server.) If you need to start implementing a software solution, there are plenty of changes and new features in SQL Server 2022 that makes it a great option. Although not yet publicly available, you can download a Preview Version from Microsoft to start using it right now.

If you are new to SQL Server, consider joining the SQL Basics in MS SQL Server course from LearnSQL.com. It includes 130 interactive SQL exercises and more than 10 hours of material. It’s part of our seven-course track SQL from A to Z in MS SQL Server, a perfect way to learn the syntax used in Microsoft SQL Server.

New Functions and Features in SQL Server 2022

Let’s see what new features and updates have been introduced in this version:

Resumable Operations

Both online index creation/rebuild operations and online primary and unique key creation operations can now be paused and resumed on demand. This lets the user break up long operations to fit into maintenance windows. It also offers the possibility of recovering from errors (for example, lack of free space) while creating indexes or constraints that require indexes.

This new feature is available for ALTER TABLE (while creating constraints) and ALTER INDEX.

ALTER TABLE Syntax

The new clause WITH RESUMABLE = { ON | OFF} [, MAX_DURATION = minutes] allows the command to be declared resumable. The optional MAX_DURATION clause specifies the number of minutes the operation can run before being automatically paused.

Example:

ALTER TABLE Invoice 
	ADD CONSTRAINT PK_Invoice PRIMARY KEY CLUSTERED (InvoiceID)
	WITH (ONLINE = ON, MAXDOP = 2, RESUMABLE = ON, MAX_DURATION = 240);

ALTER INDEX Syntax

Syntax for ALTER INDEX allows us to resume, pause, or abort a resumable index creation. The syntax uses the ALL option rather than a specific index name:

ALTER INDEX ALL ON   
      { RESUME [WITH (,[...n])] | PAUSE | ABORT }

Example:

ALTER INDEX ALL ON Invoice RESUME;

AUTO_DROP Statistics

Manually-created statistics may prevent schema changes being applied correctly. Starting with SQL Server 2022, such statistics can be created (or updated) with the AUTO_DROP option to avoid this kind of situation.

CREATE STATISTICS Syntax

The syntax to create statistics that can be automatically removed if they block a schema change is:

CREATE STATISTICS ... WITH AUTO_DROP = ON;

UPDATE STATISTICS Syntax

The syntax to update statistics that can be automatically removed if they block a schema change is:

UPDATE STATISTICS ... WITH AUTO_DROP = ON;

Example:

CREATE STATISTICS InvoiceStats ON Invoice (CustomerKey, InvoiceDate)
WITH AUTO_DROP = ON;

SELECT – WINDOW Clause

This allows us to define one or many window clauses at the SELECT level and reference them one or multiple times at the column level when using any windowing function.

Syntax:

WINDOW window_name AS (
       [ reference_window_name ]   
       [ <PARTITION BY clause> ]  
       [ <ORDER BY clause> ]   
       [ <ROW or RANGE clause> ] )  

The PARTITION BY, ORDER BY and ROW/RANGE clauses are defined exactly as in the OVER clause of any windowing function. Multiple named windows can be defined and they can reference a previously defined window.

Example:

SELECT CustID, InvoiceID, ProductID, Amount,
    SUM(Amount) OVER winPart AS Total,
    AVG(Amount) OVER winPart AS Average,
    LAG(Amount) OVER winOrder AS Previous,
    LEAD(Amount) OVER winOrder AS Next
FROM Sales.Invoices
WHERE InvoiceDate >= '2022-01-01'
WINDOW winPart AS (PARTITION BY CustID),
	winOrder AS (winPart ORDER BY InvoiceID)
ORDER BY CustID, InvoiceID;

This new feature saves time and avoids errors when window functions are used multiple times in a single query. Prior to SQL Server 2022, the same results could be achieved by writing the following:

SELECT CustID, InvoiceID, ProductID, Amount,
    SUM(Amount) OVER (PARTITION BY CustID) AS Total,
    AVG(Amount) OVER (PARTITION BY CustID) AS Average,
    LAG(Amount) OVER (PARTITION BY CustID ORDER BY InvoiceID) AS Previous,
    LEAD(Amount) OVER (PARTITION BY CustID ORDER BY InvoiceID) AS Next
FROM Sales.Invoices
WHERE InvoiceDate >= '2022-01-01'
ORDER BY CustID, InvoiceID;

With the new syntax, we can define windows more efficiently. And we can easily change the partitioning from “CustID” to “ProductID” in a single place rather than having to update all the column definitions in the SELECT clause.

To learn more about Window Functions in SQL Server, take a look at the article What Are Window Functions in SQL.

IS [NOT] DISTINCT FROM Operator

This new operator compares two expressions and always returns TRUE or FALSE – even if any of the operands are NULL. The following table shows the how this operator behaves when there are null values involved:

ExpA	ExpB	ExpA = ExpB	ExpA IS NOT DISTINCT FROM ExpB
0	0	True	True
0	1	False	False
0	NULL	Unknown	False
NULL	NULL	Unknown	True

This operator can be used in the WHERE and HAVING clauses, in the join conditions of FROM clause, and in any other construct where a Boolean value is evaluated.

Syntax:

ExpressionA IS [NOT] DISTINCT FROM ExpressionB

Example:

SELECT * FROM Invoices WHERE SalesRepID IS DISTINCT FROM 10;

This example returns all invoices that do not belong to sales representative #10 (either because they belong to another sales representative or because they do not have a sales representative).

JSON Functions

There are some new JSON functions introduced in SQL Server 2022 that allow us to construct JSON objects and arrays or to validate the existence of a particular path in a JSON string.

JSON_PATH_EXISTS

This function confirms if a specified SQL/JSON path exists in the input JSON string.

Syntax:

JSON_PATH_EXISTS( value_expression, sql_json_path )

Example:

DECLARE @json NVARCHAR(MAX);
SET @json = N'{"info":{"State":[{"City":"Austin"},{"City":"Dallas"}]}}';
SELECT JSON_PATH_EXISTS(@json,'$.info.State');

This returns True (1), since the '$.info.State' path exists in the JSON element.

JSON_OBJECT

This function constructs a JSON object text from zero or more expressions.

Syntax:

JSON_OBJECT ( [ <json_key_value> [,...n] ] [ json_null_clause ] )
<json_key_value> ::= json_key_name : value_expression
<json_null_clause> ::=  NULL ON NULL| ABSENT ON NULL

Example:

SELECT JSON_OBJECT('name':'value', 'type':NULL ABSENT ON NULL);

This sentence returns the following JSON string:

{"name":"value"}

JSON_ARRAY

This function constructs a JSON array text from zero or more expressions.

Syntax:

JSON_ARRAY ( [ <json_array_value> [,...n] ] [ <json_null_clause> ]  )  
<json_array_value> ::= value_expression
<json_null_clause> ::=   NULL ON NULL	| ABSENT ON NULL

Example:

SELECT JSON_ARRAY('a', 1, 'b', NULL);

This sentence returns the following JSON string:

["a",1,"b"]

APPROX_PERCENTILE_CONT() & APPROX_PERCENTILE_DISC()

These two new aggregate functions are similar to existing PERCENTILE_CONT() and PERCENTILE_DISC() functions. Rather than perform an exact calculation, they obtain an approximated value. This may be an good alternative for large datasets where faster results are desirable (rather than very accurate but much slower results).

Syntax:

APPROX_PERCENTILE_CONT (numeric_literal)
	WITHIN GROUP (ORDER BY order_by_expression [ASC|DESC])

APPROX_PERCENTILE_DISC (numeric_literal)  
	WITHIN GROUP (ORDER BY order_by_expression [ASC|DESC]

Example:

SELECT DeptId,
	APPROX_PERCENTILE_DISC(0.10) WITHIN GROUP(ORDER BY Salary) AS 'Perc10',
	APPROX_PERCENTILE_DISC(0.90) WITHIN GROUP(ORDER BY Salary) AS 'Perc90'
FROM Employees
GROUP BY DeptId;

GREATEST() & LEAST()

These two new functions return greatest and least values from a list between 1 and 254 arguments. Each of them can be a constant, variable, column name, function, or even a scalar subquery if all of them are of the same datatype or can be implicitly converted to the same datatype.

Syntax:

GREATEST ( expression1 [ ,...expressionN ] )	
LEAST ( expression1 [ ,...expressionN ] )

Example:

SELECT GREATEST(100, ‘350’, POWER(4,3));

This will return a result of 350, since it is the greatest numeric value between 100, 350 and 64 numeric values.

To obtain the same results in previous SQL Server versions, MAX() aggregate function could be used in this (more complicated) way:

SELECT MAX (z.MyNumber)
FROM (SELECT 100 AS MyNumber UNION ALL 
SELECT ‘350’ AS MyNumber UNION ALL 
SELECT POWER(4,3) AS MyNumber ) z;

DATETRUNC()

This new function allows the removal of parts of a date value. It is similar to the TRUNC() function, but used with numbers.

Syntax:

DATETRUNC ( datepart, date )

Example:

This example will show some of the date parts that can be used in the function:

DECLARE @d datetime2 = '2022-08-15 17:25:20.1234567';
SELECT 'Year' AS Period, DATETRUNC(year, @d) AS TruncDate;
SELECT 'Month' AS Period, DATETRUNC(month, @d) AS TruncDate;
SELECT 'Day' AS Period, DATETRUNC(day, @d) AS TruncDate;
SELECT 'Minute' AS Period, DATETRUNC(minute, @d) AS TruncDate;
SELECT 'Microsecond' AS Period, DATETRUNC(microsecond, @d) AS TruncDate;

And the results of executing those sentences will be (highlighted in red the “truncated” section of the source value):

Period	TrunDate
Year	2022-01-01 00:00:00.0000000
Month	2022-08-01 00:00:00.0000000
Day	2022-08-15 00:00:00.0000000
Minute	2022-08-15 17:25:00.0000000
Microsecond	2022-08-15 17:25:20.1234560

Bit Manipulating Functions

There are five new bit manipulation functions in SQL Server 2022 that allow for the better processing and storage of binary data. They are designed to operate with the TINYINT, SMALLINT, INT, BIGINT, BINARY, and VARBINARY data types. These functions are as follows:

LEFT_SHIFT() & RIGHT_SHIFT

These functions take an integer or binary expression as the original value and the number of bits to shift it. Then they move the bit values of the input expression the designated number of places to the left/right.

Syntax:

LEFT_SHIFT ( expression_value, shift_amount )

Example:

SELECT LEFT_SHIFT (124, 2);

This will return a result of 496, since it “moves” the bits in the input expression two spaces to the left:

Representation	Original Value	Shifted Value
Binary	01111100	00000001 11110000
Numeric	124	496

The original bits (in green) were moved two spaces to the left; the zeros (in red) have been added to fill the two “missing” spaces. In this example, the entire expression was converted from a single byte value to a double byte value.

Negative values can be provided as a shift amount parameter. In those cases, the functions perform the reverse operation (LEFT_SHIFT() moves values to the right, and RIGHT_SHIFT() moves values to the left).

BIT_COUNT()

This function returns the number of bits with a value set to 1 in the input parameter (which must be of an integer or binary type).

Syntax:

BIT_COUNT ( expression_value)

Example:

SELECT BIT_COUNT (124);

This will return a result of 5, since there are 5 bits set to one in 124 (whose binary representation is 01111100).

GET_BIT ()

This function returns the nth bit value out of a given integer or binary expression. Bits are counted starting with 0 as the rightmost position.

Syntax:

GET_BIT ( expression_value, bit_offset )

Example:

SELECT GET_BIT (124, 1);

This will return a result of 0, since the bit in the position 1 of 124 is a 0 (the binary representation of 124 is 01111100).

SET_BIT ()

This function sets the nth bit value of a given integer or binary expression. Bits are counted starting with 0 as the rightmost position; if no value is specified, it defaults to 1 .

Syntax:

SET_BIT ( expression_value, bit_offset [, bit_value] )

Example:

SELECT SET_BIT (124, 1,1);

This will return a result of 126, since it sets the bit in position 1 of 124 to 1.

Representation	Original Value	Set Value
Binary	01111100	01111110
Numeric	124	126

GENERATE_SERIES() Table Function

This new function allows the user to generate a series of numbers (integer or decimal) , given an interval and steps defined by the user.

Syntax:

GENERATE_SERIES ( start, stop [, step ] )

Example:

To generate values between 1 and 10 with a step of 2, we use this:

SELECT value FROM GENERATE_SERIES(1, 10, 2);

The expected results are:

Value
1
3
5
7
9

Enhancements in SQL Server 2022

Besides all these new features, there are some enhancements made to existing parts of SQL Server. These are:

Conflict Handling in Peer-to-Peer Transactional Replication

This option was introduced in SQL Server 2019 Cumulative Update 13 (in October 2021) and has been made part of the initial release of SQL Server 2022.

This feature allows peer-to-peer transactional replication to use a “last-write-win“ conflict resolution approach, thus significantly reducing the number of conflicts that require intervention.

ISJSON()

This function, introduced in SQL Server 2016, now accepts a second argument that allows validation only if the initial argument is a valid JSON object and it matches a specific JSON type.

Syntax:

ISJSON ( expression [, json_type_constraint] )

The JSON type constraint argument can be:

• VALUE: Tests for a valid JSON value of any type.
• ARRAY: Tests for a valid JSON array.
• OBJECT: Tests for a valid JSON object.
• SCALAR: Tests for a valid JSON scalar – number or string.

STRING_SPLIT()

This function, introduced in SQL Server 2016, now accepts a third argument that enables or disables the “ordinal” output column (which indicates the order of each output value in the source string).

Syntax:

STRING_SPLIT ( string , separator [ , enable_ordinal ] )

Examples:

The original behavior just returns the value column:

SELECT value FROM STRING_SPLIT(‘This is a string’, ‘ ’);

The expected results are:

Value
This
is
a
string

The new argument allows the ordinal column to be shown:

SELECT * FROM STRING_SPLIT(‘This is a string’, ‘ ’, 1);

Value	Ordinal
This	1
Is	2
A	3
string	4

FIRST_VALUE() & LAST_VALUE()

These two analytical functions now accept the modifiers IGNORE NULLS or RESPECT NULLS. These allow the user to define if NULL values should be considered when obtaining the first or last value in a partition.

Syntax:

FIRST_VALUE ( [scalar_expression ] )  [ IGNORE NULLS | RESPECT NULLS ]
    OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] )
LAST_VALUE ( [ scalar_expression ] )  [ IGNORE NULLS | RESPECT NULLS ]
    OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] )

Example:

The following query returns invoice information together with the latest non-NULL value of the InvoiceInstructions column (meaning that rows with NULL values in that column will show the value from the row containing a non-null value):

SELECT CustomerID, InvoiceDate, InvoiceAmount, 
    LAST_VALUE(InvoiceInstructions) IGNORE NULLS 
    OVER (PARTITION BY CustomerID ORDER BY InvoiceDate) AS FirstInvoice
FROM Invoices;

Want to Learn More About Microsoft SQL Server?

We've taken a quick look at some of the improvements and new features in Microsoft SQL Server 2022. If you’re not fully familiar with SQL or MS SQL Server, consider taking a look at the SQL Basics in MS SQL Server or any of the comprehensive courses available at LearnSQL.com to develop your SQL Server skills.