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.