SQL Server Performance

SET ANSI_NULLS

Discussion in 'T-SQL Performance Tuning for Developers' started by HarryArchibald, Feb 18, 2003.

  1. HarryArchibald New Member

    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?
  2. Argyle New Member

    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
  3. HarryArchibald New Member

    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

Share This Page