Chad Callihan answers one of the mysteries of the universe:
Have you ever seen ANSI_NULLS being set to ON or OFF in a script or stored procedure? I’ve seen or heard it mentioned a few times recently and thought it would be worth demonstrating an example of what this setting does.
Read on for the answer, including a truth table. But definitely keep ANSI_NULLS set to ON, as several important bits of functionality in SQL Server expect it that way.