How to Decode @@OPTIONS Value? – Interview Question of the Week #268

There are situations where set options can change the query behavior of a session. The session-level options can be found using the @@options variable in SQL Server. In this blog, I am sharing a useful script which can be used to decode the value of this global variable.

There are multiple ways and formats to get the output. I found the below query in profiler which I was capturing it for some other blog.

If you have a need to get connection options set as default (in below screen). This is the property window of the server.

Here is the query.

DECLARE @ServerUserOption INT
SELECT @ServerUserOption=convert(INT, conf.value) 
FROM sys.configurations conf 
WHERE conf.name='user options';
SELECT
CAST(@ServerUserOption & 1 AS bit) AS [DisableDefaultConstraintCheck],
CAST(@ServerUserOption & 2 AS bit) AS [ImplicitTransactions],
CAST(@ServerUserOption & 4 AS bit) AS [CursorCloseOnCommit],
CAST(@ServerUserOption & 8 AS bit) AS [AnsiWarnings],
CAST(@ServerUserOption & 16 AS bit) AS [AnsiPadding],
CAST(@ServerUserOption & 32 AS bit) AS [AnsiNulls],
CAST(@ServerUserOption & 64 AS bit) AS [AbortOnArithmeticErrors],
CAST(@ServerUserOption & 128 AS bit) AS [IgnoreArithmeticErrors],
CAST(@ServerUserOption & 256 AS bit) AS [QuotedIdentifier],
CAST(@ServerUserOption & 512 AS bit) AS [NoCount],
CAST(@ServerUserOption & 1024 AS bit) AS [AnsiNullDefaultOn],
CAST(@ServerUserOption & 2048 AS bit) AS [AnsiNullDefaultOff],
CAST(@ServerUserOption & 4096 AS bit) AS [ConcatenateNullYieldsNull],
CAST(@ServerUserOption & 8192 AS bit) AS [NumericRoundAbort],
CAST(@ServerUserOption & 16384 AS bit) AS [AbortTransactionOnError]

You might wonder why it is done this way? This is the way to store multiple values true or false in one column. It’s like storing a binary value.

Let’s say you have a column to store color and it must be able to store all combinations of colors. Here is a simple table which can help you understand.

Color Binary Numeric

As you can imagine, we can have many such values stored in one column. Here is the sample which would help you in learning this concept.

SET NOCOUNT ON
GO
USE tempdb
GO
DROP TABLE IF EXISTS COLOR 
GO
CREATE TABLE COLOR (color varchar(10), options int)
GO
INSERT INTO COLOR VALUES
('x x x', 0), 
('x x R', 1),
('x G x', 2),
('B x x', 4),
('x G R', 3), 
('B G x', 6),
('B x R', 5),
('B G R', 7)
GO
DECLARE @color int
SET @color = 4;
SELECT CAST(@color & 4 AS bit) AS [B],
CAST(@color & 2 AS bit) AS [G],
CAST(@color & 1 AS bit) AS [R]

This query should give you an understanding of the main query and its logic to decode the values.

If there is a need to query SET options for the session, then the below query can be used.

Decode Options

DECLARE @LoggedInUserOption INT
SELECT @LoggedInUserOption = @@OPTIONS
SELECT
CAST(@LoggedInUserOption & 1 AS bit) AS [DisableDefaultConstraintCheck],
CAST(@LoggedInUserOption & 2 AS bit) AS [ImplicitTransactions],
CAST(@LoggedInUserOption & 4 AS bit) AS [CursorCloseOnCommit],
CAST(@LoggedInUserOption & 8 AS bit) AS [AnsiWarnings],
CAST(@LoggedInUserOption & 16 AS bit) AS [AnsiPadding],
CAST(@LoggedInUserOption & 32 AS bit) AS [AnsiNulls],
CAST(@LoggedInUserOption & 64 AS bit) AS [AbortOnArithmeticErrors],
CAST(@LoggedInUserOption & 128 AS bit) AS [IgnoreArithmeticErrors],
CAST(@LoggedInUserOption & 256 AS bit) AS [QuotedIdentifier],
CAST(@LoggedInUserOption & 512 AS bit) AS [NoCount],
CAST(@LoggedInUserOption & 1024 AS bit) AS [AnsiNullDefaultOn],
CAST(@LoggedInUserOption & 2048 AS bit) AS [AnsiNullDefaultOff],
CAST(@LoggedInUserOption & 4096 AS bit) AS [ConcatenateNullYieldsNull],
CAST(@LoggedInUserOption & 8192 AS bit) AS [NumericRoundAbort],
CAST(@LoggedInUserOption & 16384 AS bit) AS [AbortTransactionOnError]

Have you used such logic in the application? Please comment and let me know.

Reference: Pinal Dave (https://blog.sqlauthority.com)