In SQL Server 2022, why is `IS [NOT] NULL` converted to `IS [NOT] DISTINCT FROM NULL` in CHECK and filtered indexes? Is it safe?

Question

• As a foreword, I am already familiar with the differences between the IS \[NOT\] NULL and IS \[NOT\] DISTINCT FROM NULL predicates.
  • So to be clear, I am not asking what IS DISTINCT FROM NULL does, nor how it differs from other types of comparisons.
• I'm running SQL Server 2022 RC0 which was released last week.
• I redeployed my SSDT database (currently targeting Azure SQL) to this new local SQL Server 2022 box using SSDT.
  • This newly deployed database is configured in SQL Server 2022 with Compatibility Level 160 (i.e. 2022).
  • ...though the live/production deployment in Azure SQL currently has Compatibility Level 140 (SQL Server 2017), not that this matters in this specific problem.

---

My database has plenty of CHECK constraints and filtered INDEX objects. For example, lots of my tables have things like this:

CREATE TABLE dbo.Foobar (
    
    FooId      int          NOT NULL IDENTITY,
    Foo        varchar(100)     NULL,

    CONSTRAINT PK_Foobar PRIMARY KEY ( FooId ),

    CONSTRAINT CK_Foo_is_null_or_trimmed_nonempty CHECK (
        ( SomeNvarcharColumn IS NULL )
        OR
        (
            LEN( SomeNvarcharColumn ) > 0
            AND
            SomeNvarcharColumn NOT LIKE ' _%'
        )
);
GO

CREATE UNIQUE NONCLUSTERED INDEX UX_DistinctFooWhenNotNull ON 
    dbo.Foobar ( Foo )
WHERE
    ( Foo IS NOT NULL );

After doing a Database Schema Comparison in SSDT when publishing to my SQL2022 box, the _{always-broken-in-some-way-or-another} comparison report showed that all of the IS NULL and IS NOT NULL comparisons in all of my CHECK constraints and filtered indexes with IS NOT DISTINCT FROM NULL and IS DISTINCT FROM NULL respectively.

So the above CREATE TABLE now looks like this in SQL Server 2022:

CREATE TABLE dbo.Foobar (
    
    FooId      int          NOT NULL IDENTITY,
    Foo        varchar(100)     NULL,

    CONSTRAINT PK_Foobar PRIMARY KEY ( FooId ),

    CONSTRAINT CK_Foo_is_null_or_trimmed_nonempty CHECK (
        ( SomeNvarcharColumn IS NOT DISTINCT FROM NULL )
        OR
        (
            LEN( SomeNvarcharColumn ) > 0
            AND
            SomeNvarcharColumn NOT LIKE ' _%'
        )
);
GO

CREATE UNIQUE NONCLUSTERED INDEX UX_DistinctFooWhenNotNull ON 
    dbo.Foobar ( Foo )
WHERE
    ( Foo IS DISTINCT FROM NULL );

My problem is that I'm not absolutely certain that x IS NULL is always, 100% equivalent (i.e. identical) to x IS NOT DISTINCT FROM NULL under all conditions (and similarly, that x IS NOT NULL is identical to x IS DISTINCT FROM NULL) - but are they really?

Assuming they is identical, then why does SQL Server 2022 convert these expressions to an (annoyingly) far more verbose form? For what gain or advantage? (Certianly not expressiveness...), and why only in filtered indexes and CHECK constraints but not other expressions?

...and does this introduce any breaking changes?

Answer 1

Using this operator "IS [NOT] DISTINCT FROM" when you want to compare two columns that have NULL is very practical and convenient. We know that two columns that contain NULLs can never be the same because NULLs are unknown and can never be equal to anything else, not even another NULL.

Sometimes you may want to treat NULLs as the same: For example, these two codes do exactly the same thing:

... WHERE (Column1 = Column2) or (Column1 IS NULL AND Column2 IS NULL)

and

... WHERE Column1 IS NOT DISTINCT FROM Column2

this syntax was added to SQL DB2 in version 8.