When I export the entire list of stored procedures/functions, I noticed there is a mismatch between ANSI_NULLS
/QUOTED_IDENTIFIERS
settings. Some have them on, others off. I'm not explicitly setting this for the stored procedures or functions, so how does SQL determine whether to define on or off for these settings by default?
SQL Server 2008 R2 – Fix ANSI_NULLS and QUOTED_IDENTIFIERS Mismatch
configurationmetadatasql serversql-server-2008-r2
Related Question
- Sql-server – Are #tables and #procedures ‘not best practice’ or ‘bad practice’
- Sql-server – ny way to set stored procedure options like ANSI_NULLS to ON or OFF outside of creation
- Sql-server – Why is the MSDB database TRUSTWORTHY
- SQL Server UDF or Stored Procedure – Limiting External Access
- Sql-server – The EXECUTE permission was denied on the object ‘SPROC’, database ‘DATABASE’, schema ‘dbo’
- sql-server,sql-server-2008,data-synchronization,schema-copy – How to Sync Database Table Schema and Stored Procedures
Best Answer
BOL quotes - 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 ofSET ANSI_NULLS
is restored to its originally used value and takes effect. When invoked inside a stored procedure, the setting ofSET ANSI_NULLS
is not changed.You can use the
sys.sql_modules
to check the stored procedure or function usesuses_ansi_nulls
anduses_quoted_identifier
settings 1 (ON) or 0 (OFF)Both
ANSI_NULLS
andQUOTED_IDENTIFIERS
should be ON so that you dont see any unexpected surprises. Also, in newer versions of SQL Server, this two settings are defaulted to ON.