SQL Server Performance Forum – Threads Archive

SET ANSI_NULLS

I am trying to confirm the effect of using set ansi_nulls in a stored procedure. BOL states that ‘When invoked inside a stored procedure, the setting of SET ANSI_NULLS is not changed.’. Does this mean that the only effect of using this statement is to cause the stored procdedure to recompile?

---

SET ANSI_NULLS as mentioned in BOL has no affect inside in a stored procedure. It’s intended to be used outside the create stored proecdure statment and then the ansi null setting you choose will stick to that stored procedure. Or did you mean that one could use the statment inside a stored procedure to cause a recompile on purpuse? /Argyle

---

I am investigating recompilations of stored procedures on a production server and found one stored procedure that is recompiled hundreds of times a day. I tracked the cause down to the SET ANSI_NULLS statement and wanted to confirm that it has no effect in a stored procedure. So now I know to remove it. Cheers,
Harry

---

]]>