Why does SQL Server require ansi_nulls to be on to create an index?

Question

Some actions in SQL Server will throw an error if executed after SET ANSI_NULLS OFF. The remedy is simple, I just have to SET ANSI_NULLS ON (and make sure my queries aren't adversely affected by the different way it compares nulls).

This is known behaviour. The official documentation says this:

ANSI_NULLS is one of seven SET options that must be set to required values when dealing with indexes on computed columns or indexed views.

But why is it so? Is there a technical reason why this variable must be set to ON?

question from:https://stackoverflow.com/questions/65713809/why-does-sql-server-require-ansi-nulls-to-be-on-to-create-an-index

Answer 1

It has a base in being deterministic.

A calculated column or an indexed view for example must always return the same data no matter what the session settings are, and therefore they are forced to behave in a consistent manner by the engine.

The example here in the indexed view page shows one example of where different options can change the behaviour. Because a calculation could in some cases use an equality comparison between columns, consistent behaviour is needed here too