Set ANIS_NULLS
For something like
select * from foo1 where a not in (select a from foo2)
If anis_nulls is ON and if the table foo2 is empty no records from foo1 are selected.
If this comparision statement exits inside a stored procedure then ANSI_NULLS setting should be done before the compilation of the stored procedure, if the set statement is inside the procedure, the ANSI_NULL setting is not affected.
From BOL:
For stored procedures, SQL Server uses the SET ANSI_NULLS setting value from the initial creation time of the stored procedure. Whenever the stored procedure is subsequently executed, the setting of SET ANSI_NULLS is restored to its originally used value and takes effect. When invoked inside a stored procedure, the setting of SET ANSI_NULLS is not changed.
I never knew this. so when today there was a bug in a stored procedure I tried to debug it, I was not able to find anything wrong with it, since the same script was working on my local machine, after drilling it down to NULL comparision
found the above thing. so recompiled the sp with set options at the start before the create statement which solved the problem.
Also remember: The setting of SET ANSI_NULLS is set at execute or run time and not at parse time.