How to put MSSQL 2022 into backward compatibility mode?

David Teviotdale 0 Reputation points
2023-07-10T23:23:27.15+00:00

Our product won't install on MSSQL 2022 due to check constraints being processed differently for columns that allow nulls.
Specifically, this check constraint

check(IsLaser in (0, 1))

on MSSQL 2022 it becomes

ADD CHECK ((([IsLaser] IS NOT DISTINCT FROM (1) OR [IsLaser] IS NOT DISTINCT FROM (0))))

when it is

ADD CHECK (([IsLaser] = (1) OR [IsLaser] = (0)))

on MSSQL 2017. Note that the 2017 version allows nulls, the 2022 version does not.

I need to know how to set a MSSQL 2022 server into a “backward compatibility mode” that will generate the older check constraint.
Note, I’m aware of how to set individual databases on MSSQL 2022 into compatibility mode (via Management Studio -> Database Properties -> Options -> Compatibility Level). But this requires that the database be created before the compatibility mode can be set. I’m looking for setting at the server level.

Setting at the database level won’t work for us, because the install script creates the database, then installs the tables, etc. So, we’d need to modify the install script. And we want to avoid having to change our install script. As we’ve already released it.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,234 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. ZoeHui-MSFT 30,411 Reputation points
    2023-07-11T02:47:28.04+00:00

    Hi @David Teviotdale,

    Compatibility is a database setting, so you could not set backward compatibility mode at the server level.

    You may use code to set the COMPATIBILITY_LEVEL when alter the database.

    ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = { 160 | 150 | 140 | 130 | 120 | 110 | 100 | 90 }
    

    Regards,

    Zoe Hui


    If the answer is helpful, please click "Accept Answer" and upvote it.

    0 comments No comments

  2. Erland Sommarskog 98,636 Reputation points
    2023-07-11T08:15:02.7466667+00:00

    Can you please show the full constraint? The AND suggests there is more.

    I run this on SQL 2022:

     CREATE TABLE #tempo (IsLaser int CHECK (IsLaser IN(0, 1)))
     INSERT #tempo (IsLaser) VALUES (NULL)
     go
     EXEC sp_help #tempo
     go
     DROP TABLE #tempo
    

    There are no errors, and the definition of the constraint is listed as

    ([IsLaser]=(1) OR [IsLaser]=(0))
    

    In any case, there is no way you can change how the constraint is stored internally. If it is stored in a way that it does not permit values it should permit, it's a bug. But we need to see the full story, so that's why I asked for the full constraint. Even better if you can share the full table definition and sample data that demonstrates the problem.


  3. Erland Sommarskog 98,636 Reputation points
    2023-07-13T08:46:17.6566667+00:00

    Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) Oct 8 2022 05:58:25 Copyright (C) 2022 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

    I suspected so. I have not gone back and checked the fix lists for the Cumulative Updates released for SQL 2022, but I am quite sure that this is a bug that has been fixed.

    So the answer to the question is that you should download and install the most recent Cumulative Updates for SQL 2022, which is CU5 (which is the version I tested on).

    Overall, you should regularly keep an eye of the Cumulative Updates that Microsoft puts out. Now, when SQL 2022, you can expect a CU each month. The pace will slow down after that, but there will still be a couple per year. If you have the habit of regularly installing CUs, you avoid the risk of running into known issues that has been fixed.