Determine ANSI_DEFAULTS setting ON or OFF? How can I determine if ANSI_DEFAULTS is enabled or not on my server. My server is using IMPLICIT transactions and one way from which it can affect it based on setting of ANSI_DEFAULTS. 

How can I determine state of ANSI_DEFAULTS?
ANSI_DEFAULTS isn't really a distinct option, but a logical grouping of several other session-based options:


IMPLICIT_TRANSACTIONS
CURSOR_CLOSE_ON_COMMIT
ANSI_WARNINGS
ANSI_PADDING
ANSI_NULLS
QUOTED_IDENTIFIER
ANSI_NULL_DFLT_ON


You can use the @@OPTIONS configuration function to get the bit-masked value of the current Session settings, the values of which are noted on the Configure the user options Server Configuration Option MSDN page.

Putting those elements together gets you the following query which determines the current session setting of the "ansi_defaults" group of options:

SELECT @@OPTIONS AS [user_options],
       CASE WHEN @@OPTIONS &amp; 2 = 2 THEN 'ON' ELSE 'OFF' END AS [implicit_transactions],
       CASE WHEN @@OPTIONS &amp; 4 = 4 THEN 'ON' ELSE 'OFF' END AS [cursor_close_on_commit],
       CASE WHEN @@OPTIONS &amp; 8 = 8 THEN 'ON' ELSE 'OFF' END AS [ansi_warnings],
       CASE WHEN @@OPTIONS &amp; 16 = 16 THEN 'ON' ELSE 'OFF' END AS [ansi_padding],
       CASE WHEN @@OPTIONS &amp; 32 = 32 THEN 'ON' ELSE 'OFF' END AS [ansi_nulls],
       CASE WHEN @@OPTIONS &amp; 256 = 256 THEN 'ON' ELSE 'OFF' END AS [quoted_identifier],
       CASE WHEN @@OPTIONS &amp; 1024 = 1024 THEN 'ON' ELSE 'OFF' END AS [ansi_null_dflt_on],
       -- all above options combined
       CASE WHEN @@OPTIONS &amp; 1342 = 1342 THEN 'ON' ELSE 'OFF' END AS [ansi_defaults]


When all of the base options are set to ON, then ANSI_DEFAULTS will be shown as being ON. If any of those options is OFF, then ANSI_DEFAULTS will also be OFF. You can see this behavior via the following test:

SET ANSI_DEFAULTS ON;

DBCC USEROPTIONS; -- ansi_defaults is SET

SET ANSI_NULLS OFF;

DBCC USEROPTIONS; -- ansi_nulls AND ansi_defaults are missing

SET ANSI_NULLS ON;

DBCC USEROPTIONS; -- ansi_nulls AND ansi_defaults are back


Setting ANSI_DEFAULTS to ON will set all of those options to ON. Likewise, setting ANSI_DEFAULTS to OFF will set all of those options to OFF.