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?


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

| improve this answer | |
  • "Also, in newer versions of SQL Server" but not necessarily the clients themselves, some of them like sqlcmd will default turn this off. – Evan Carroll Jan 13 '18 at 21:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.