There is a variable in SQL Server called @@OPTIONS. This contains all of the settings that you have configured using the SET command. However, understanding the values inside of this variable can be a bit cumbersome. This article will show you a quick way to determine what settings you have in your environment.
Decoding @@Options
The @@Options variable contains an integer. This integer contains the values of all the SET options. As an example, I can run this:
What does this mean? The values are stored as a bitmap, which means that this value, 5496, is made up of a binary string. This means that these are equivalent
5496 = 001010101111000
The left side is decimal, or base 10. The right side is binary, or base 2. Each place in the number on the right represents a setting that is used by SQL Server. For example, the first 1 (leftmost) is for the setting CONCAT_NULL_YIELDS_NULL. This is set to 1.
Note, in this case, there are actually leading zeros in the number since there are more settings than shown.
There is a value for each place in the right, binary, number. If we start reading from the right, we see 0,0,0,1. These values for the places are: 1,2,4,8. We're counting in powers of 2 here. This means the entire number is this, reading left to right.
- 0 - 16384
- 0 - 8192
- 1 - 4096
- 0 - 2048
- 1 - 1024
- 0 - 512
- 1 - 256
- 0 - 128
- 1 - 64
- 1 - 32
- 1 - 16
- 1 - 8
- 0 - 4
- 0 - 2
- 0 - 1
I can use a bitwise operation to actually get these values from the decimal 5496 (or @@OPTIONS). For example, if I do this:
SELECT 5496 & 8
This returns the value 8. I've done a bitwise operation, which will return 1000. There are actually other, leading zeros, but in this case, the base 2 1000 is the value 8 in decimal.
I can use this technique to decode all of the settings in the @@OPTIONS variable. I can use a bitwise operation with powers of 2 and then capture the values. If the value is 0, the setting is off. A setting that equals the value of the power of two means the setting is on. I could use a series of statements like this:
IF ((@@OPTIONS & 1) = 1)
SELECT 'IMPLICIT_TRANSACTIONS are ON';
IF ((@@OPTIONS & 2) = 2)
SELECT 'IMPLICIT_TRANSACTIONS are ON';
IF ((@@OPTIONS & 4) = 4)
SELECT 'CURSOR_CLOSE_ON_COMMIT is ON';
IF ((@@OPTIONS & 8) = 8)
SELECT 'ANSI_WARNINGS';
IF ((@@OPTIONS & 16) = 16)
SELECT 'ANSI_PADDING';I think there's a better way.
Keeping This Handy
I decided that a stored procedure that will easily get the settings would be the best choice. Functions work, but then I have to do some SELECT * from Function, which is harder to remember. I'd prefer to:
EXEC sp_GetSetOptions
To build the procedure, I had to decide on whether to return a wide result set or a long one. I'll actually give you both, but I prefer the long one. This necessitates me UNIONing a bunch of SELECT statements. To go wide, I would make each operation a separate item in the SELECT list.
The basic structure is what is shown above, wrapped in a stored procedure. I take the @@OPTIONS value and then perform a bitwise operation against this with different values. Each of these is then part of a SELECT, and I include the value used in the result set to make things easy. Here is the code:
USE master
GO
CREATE OR ALTER PROCEDURE dbo.sp_GetSetOptions
AS
DECLARE @o INT
SELECT @o = @@OPTIONS;
SELECT CASE
WHEN (@o & 1) = 0 THEN
'Disable_DEFERRED_CONSTRAINT_Checking IS OFF'
ELSE
'Disable_DEFERRED_CONSTRAINT_Checking IS ON'
END AS [Option Setting]
, 1 AS [BinaryValue]
UNION
SELECT CASE
WHEN (@o & 2) = 0 THEN
'Implicit Transactions are OFF'
ELSE
'Implicit Transactions are ON'
END
, 2
UNION
SELECT CASE
WHEN (@o & 4) = 0 THEN
'Close cursor on commit is OFF'
ELSE
'Close cursor on commit is ON'
END
, 4
UNION
SELECT CASE
WHEN (@o & 8) = 0 THEN
'ANSI Warnings are OFF'
ELSE
'ANSI Warnings are ON'
END
, 8
UNION
SELECT CASE
WHEN (@o & 16) = 0 THEN
'ANSI PADDING is OFF'
ELSE
'ANSI PADDING is ON'
END
, 16
UNION
SELECT CASE
WHEN (@o & 32) = 0 THEN
'ANSI NULLs are OFF'
ELSE
'ANSI NULLs are ON'
END
, 32
UNION
SELECT CASE
WHEN (@o & 64) = 0 THEN
'ARITHABORT is OFF'
ELSE
'ARITHABORT is ON'
END
, 64
UNION
SELECT CASE
WHEN (@o & 128) = 0 THEN
'ARITHIGNORE is OFF'
ELSE
'ARITHIGNORE is ON'
END
, 128
UNION
SELECT CASE
WHEN (@o & 256) = 0 THEN
'Quoted Identifers is OFF'
ELSE
'Quoted Identifers is ON'
END
, 256
UNION
SELECT CASE
WHEN (@o & 512) = 0 THEN
'NOCOUNT is OFF'
ELSE
'NOCOUNT is ON'
END
,512
UNION
SELECT CASE
WHEN (@o & 1024) = 0 THEN
'ANSI NULL Defaults ON is set'
WHEN (@o & 2048) = 0 THEN
'ANSI NULL Defaults OFF is set'
ELSE
'ANSI NULL Defaults ON is NOT set'
END
, 1024
UNION
SELECT CASE
WHEN (@o & 4096) = 0 THEN
'CONCAT_NULL_YIELDS_NULL is OFF'
ELSE
'CONCAT_NULL_YIELDS_NULL is ON'
END
,4096
UNION
SELECT CASE
WHEN (@o & 8192) = 0 THEN
'NUMERIC_ROUNDABORT is OFF'
ELSE
'NUMERIC_ROUNDABORT is ON'
END
,8192
UNION
SELECT CASE
WHEN (@o & 16384) = 0 THEN
'XACT Abort is OFF'
ELSE
'XACT Abort is ON'
END
, 16384
I decided to put this in master so I can quickly query it from anywhere. I know this isn't recommended, but this is a utility procedure I use when we sometimes have strange behavior from T-SQL among different members of our team.
When I run this, I get a result like this:
I don't care about ordering, because I am usually looking at the various settings and then comparing them with a set of results from another machine. This format works well for me.
If I wanted a wide result set, I'd do something like this.
USE master
GO
CREATE OR ALTER PROCEDURE dbo.sp_GetSetOptions2
AS
DECLARE @o INT
SELECT @o = @@OPTIONS;
SELECT CASE
WHEN (@o & 1) = 0 THEN
'Disable_DEFERRED_CONSTRAINT_Checking IS OFF'
ELSE
'Disable_DEFERRED_CONSTRAINT_Checking IS ON'
END AS [BitMap 1]
, CASE
WHEN (@o & 2) = 0 THEN
'Implicit Transactions are OFF'
ELSE
'Implicit Transactions are ON'
END AS [BitMap 2]
, CASE
WHEN (@o & 4) = 0 THEN
'Close cursor on commit is OFF'
ELSE
'Close cursor on commit is ON'
END AS [BitMap 4]
,CASE
WHEN (@o & 8) = 0 THEN
'ANSI Warnings are OFF'
ELSE
'ANSI Warnings are ON'
END AS [BitMap 8]
,CASE
WHEN (@o & 16) = 0 THEN
'ANSI PADDING is OFF'
ELSE
'ANSI PADDING is ON'
END AS [BitMap 16]
,CASE
WHEN (@o & 32) = 0 THEN
'ANSI NULLs are OFF'
ELSE
'ANSI NULLs are ON'
END AS [BitMap 32]
,CASE
WHEN (@o & 64) = 0 THEN
'ARITHABORT is OFF'
ELSE
'ARITHABORT is ON'
END AS [BitMap 64]
, CASE
WHEN (@o & 128) = 0 THEN
'ARITHIGNORE is OFF'
ELSE
'ARITHIGNORE is ON'
END AS [BitMap 128]
, CASE
WHEN (@o & 256) = 0 THEN
'Quoted Identifers is OFF'
ELSE
'Quoted Identifers is ON'
END AS [BitMap 256]
, CASE
WHEN (@o & 512) = 0 THEN
'NOCOUNT is OFF'
ELSE
'NOCOUNT is ON'
END AS [BitMap 512]
, CASE
WHEN (@o & 1024) = 0 THEN
'ANSI NULL Defaults ON is set'
WHEN (@o & 2048) = 0 THEN
'ANSI NULL Defaults OFF is set'
ELSE
'ANSI NULL Defaults ON is NOT set'
END AS [BitMap 1024 and 2048]
, CASE
WHEN (@o & 4096) = 0 THEN
'CONCAT_NULL_YIELDS_NULL is OFF'
ELSE
'CONCAT_NULL_YIELDS_NULL is ON'
END AS [BitMap 4096]
, CASE
WHEN (@o & 8192) = 0 THEN
'NUMERIC_ROUNDABORT is OFF'
ELSE
'NUMERIC_ROUNDABORT is ON'
END AS [BitMap 8192]
, CASE
WHEN (@o & 16384) = 0 THEN
'XACT Abort is OFF'
ELSE
'XACT Abort is ON'
END AS [BitMap 16384]
In this case, I included the bitwise value as the column name.
With either way, I can quickly get a set of option values decoded in my environment. Usually I'll do something like this in two sessions:
CREATE TABLE ##options(optionvalue varchar(200), bitwise int) INSERT ##options EXEC dbo.sp_GetSetOptions
I'll repeat this code with a "2" for the table in another session. I can then run this code to determine if two clients have any different settings.
SELECT * FROM ##options2 AS o2 INNER JOIN ##options AS o ON o.bitwise = o2.bitwise AND o2.optionvalue != o.optionvalue
Summary
Many of us rely on defaults for the creation of objects and execution of our code. In many cases, things are very consistent and this isn't an issue. However, there can be strange behaviors when defaults are changed or new team members configure systems differently. Having a way to quickly determine if our settings are the same is valuable.
Since I work in a team with people located in different countries, we sometimes find that settings have been changed, or a developer is experimenting and makes changes. When we can determine that an option has changed, we can more quickly debug the issues.
Hopefully you find this useful.
References
These are the links I used for this article:
