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
COLOR | BINARY | NUMERIC |
x x x | 0 0 0 | 0 |
x x R | 0 0 1 | 1 |
x G x | 0 1 0 | 2 |
B x x | 1 0 0 | 4 |
x G R | 0 1 1 | 0 + 2 + 1 = 3 |
B G x | 1 1 0 | 4 + 2 + 0 = 6 |
B x R | 1 0 1 | 4 + 0 + 1 = 5 |
B G R | 1 1 1 | 4 + 2 + 1 = 7 |
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)
1 Comment. Leave new
Hi Pinal,
here is my solution:
SELECT [name] AS CONFIGURATION
,CASE SIGN(@@OPTIONS & number)
WHEN 1 THEN ‘ON’
WHEN 0 THEN ‘OFF’
END AS setting
FROM master.dbo.spt_values
WHERE [type] = ‘SOP’ AND number > 0
ORDER BY number;