ANSI_NULLS and QUOTED_IDENTIFIERS mismatch

by Brian Mains   Last Updated January 13, 2018 22:06 PM

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?



Answers 1


so how does SQL determine whether to define on or off for the ansi nulls by default?

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 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.

You can use the sys.sql_modules to check the stored procedure or function uses uses_ansi_nulls and uses_quoted_identifier settings 1 (ON) or 0 (OFF)

SELECT OBJECT_NAME([object_id]) as ObjectName, 
       uses_ansi_nulls, 
       uses_quoted_identifier
FROM sys.sql_modules
--WHERE OBJECT_NAME([object_id]) = 'any_sp/function_name_your_Want_to_Check'
ORDER BY object_id

Both ANSI_NULLS and QUOTED_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.

Kin
Kin
September 27, 2015 02:41 AM

Related Questions



Quoting columns with spaces in PostgreSQL?

Updated October 26, 2017 22:06 PM

Blitz procedure failing

Updated January 13, 2018 21:06 PM

Strange behaviours with SET options in SSMS

Updated January 13, 2018 21:06 PM

SQL Server Agent: QUOTED_IDENTIFIER

Updated January 13, 2018 21:06 PM