When I export the entire list of stored procedures/functions, I noticed there is a mismatch between
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?
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_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
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.