Share via

SET OPTIONS

Bhadiyadra, Manoj (Pune) 21 Reputation points
2024-02-09T09:08:26.1933333+00:00

Microsoft deprecated - ANSI_NULLS OFF in SQL Server 2022. What will be the alternate for this option. As a deprecated feature this one will stop working in the upcoming SQL Server versions. So what will be the alternate for this option. I am happy to hear from Microsoft.

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,363 questions
0 comments
Accepted answer
  1. Greg Low 1,685 Reputation points Microsoft Regional Director
    2024-02-09T11:04:39.0166667+00:00

    Obviously the ideal outcome would be to decide if you really need this option. It's a very old option and encourages poor NULL handling logic. In a normal SQL system, comparing anything to NULL returns NULL not true or false. This option changes that, but not for the better. Now that said, I understand you might have a bunch of code that depends on it. Given it's only recently deprecated (and I'm pretty sure it was in 2017, not 2022), I wouldn't be too worried about it disappearing on you. They also say that nowadays they try not to remove deprecated features. Most features deprecated 12 years earlier in 2005 are still present in the product. But I would encourage you to improve the logic in the code so that option is not required. You can easily apply slightly different logic in situations where you were depending upon this outcome, and at least those options would then live on without issue.

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Amira Bedhiafi 20,176 Reputation points
    2024-02-09T10:58:55.4266667+00:00
    1 person found this answer helpful.
  2. Erland Sommarskog 107.2K Reputation points
    2024-02-09T22:45:09.8966667+00:00

    They basically stopped listing deprecated features with the release of SQL 2019.

    But have no hesitation: don't use SET ANSI_NULLS OFF. If you have code that relies on this setting, fix that code. Beware that there are several features in SQL Server that requires that ANSI_NULLS to be ON (together with a few more SET OPTIONS), and you will be burnt by this sooner or later.

    Yes, it can be some work to go through your code, but you have had more than twenty years to do so. I recall that we did this in a large system I worked with. Staying with ANSI_NULLS OFF was simply not an option.

    0 comments