Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.