Apparently, SQL Server stores the current ANSI_NULL setting when creating a table (see related questions here and here).
However, when I execute
SELECT * FROM myVeryOldTable WHERE someFieldWithNullValues = NULL
I get zero results, even though the old table was created with ANSI_NULL OFF. So, apparently, SQL Server uses the current connection setting, which is ANSI_NULL ON.
If that is the case, what difference does the setting stored in the table make (except for making trouble in various cases)? Why is it stored at all, if the connection setting always overrides the table setting?
Best Answer
The table-level ANSI_NULLS setting matters for automatic table-level operations like when processing computed columns. This also applies to other objects such as triggers (as seen here).
For operations against the table (i.e. comparisons in queries) the connection applies though - this can be quite confusing if you are not expecting a difference in behaviour between what you actively do and automatic operations.