Additional T-SQL Improvements in SQL Server 2022

Recently, we explored several core T-SQL enhancements introduced in SQL Server 2022 CTP 2.0, alongside Aaron Bertrand's insightful coverage of early additions. Following those updates, Microsoft released SQL Server 2022 CTP 2.1, bringing a new wave of powerful T-SQL improvements. These include:

• The Distinct Predicate (IS [NOT] DISTINCT FROM)

• Approximate Percentile Functions (APPROX_PERCENTILE_CONT and APPROX_PERCENTILE_DISC)

• The DATETRUNC function

• A comprehensive set of bit manipulation functions

In this article, we will focus deeply on the distinct predicate and the approximate percentile functions. We will reserve DATETRUNC and the bit manipulation functions for upcoming installments.

(Note: All examples in this article utilize the TSQLV6 sample database, which you can download from official Microsoft sample repositories.)

1. The Distinct Predicate (IS [NOT] DISTINCT FROM)

If you have spent years writing and optimizing T-SQL code, you are likely intimately familiar with the complexities of handling NULL values. Specifically, comparisons involving potentially NULL operands—such as those in WHERE filter clauses and JOIN predicates—frequently trip up developers.

The Three-Valued Logic Challenge
Most standard comparison operators (including equals = and not-equals <>) rely on three-valued logic. When evaluating a predicate:

• If both comparands are non-NULL, the operator evaluates to True or False.

• If any comparand is NULL (even when both are NULL), the comparison evaluates to Unknown.

Filter and join predicates discard any row where the condition evaluates to Unknown.

To illustrate this, consider the following query using a local variable (representing a stored procedure parameter @dt):

DECLARE @dt AS DATE = '20220212'; SELECT orderid, shippeddate FROM Sales.Orders WHERE shippeddate = @dt; 

When passing a valid non-NULL date ('20220212'), the query successfully returns matching orders:

orderid shippeddate ----------- ----------- 10865 2022-02-12 10866 2022-02-12 10876 2022-02-12 10878 2022-02-12 10879 2022-02-12 

Here, matching rows evaluate to True, differing non-NULL rows evaluate to False, and unshipped orders (where shippeddate IS NULL) …