While browsing StackOverflow, I came across an interesting use case for the Set Options feature in SQL Server. I have created a few YouTube videos on the different SET options available at the database level, see links below. In this case the user wanted to use the @@options function to track the Options that are SET then revert back to the same values at a later point in time. I don’t know why they would be doing this I assume it is for troubleshooting a issue. Event then I am not sure this is the best way to do it. With that said we need to find a quick and easy way to get the Options that are SET when the session is created.
The Trace
There are number of places where you can gather the SET Options, one of the first places a developer sees the SET Options is when they start a Trace in SQL profiler. Unfortunately these values cant be extracted easily and don’t lend them selves easily to troubleshooting. The below screenshot is a example of what you would see in Profiler.
The DMV
There is another way developers can gather the Set Options for a session and this is under the below DMVs, these are again dependent on the context in which the Set Option is applicable. For example some SET options work at the database level while others at the session level.
select
*
from
sys.dm_exec_sessions
select
*
from
sys.databases
The Query
SELECT
GET_BIT(@@OPTIONS, 0) /* 1 */ AS [DISABLE_DEF_CNST_CHK] -- Controls interim or deferred constraint checking.
, GET_BIT(@@OPTIONS, 1) /* 2 */ AS [IMPLICIT_TRANSACTIONS] -- For dblib network library connections, controls whether a transaction is started implicitly when a statement is executed. The IMPLICIT_TRANSACTIONS setting has no effect on ODBC or OLEDB connections.
, GET_BIT(@@OPTIONS, 2) /* 4 */ AS [CURSOR_CLOSE_ON_COMMIT] -- Controls behavior of cursors after a commit operation has been performed.
, GET_BIT(@@OPTIONS, 3) /* 8 */ AS [ANSI_WARNINGS] -- Controls truncation and NULL in aggregate warnings.
, GET_BIT(@@OPTIONS, 4) /* 16 */ AS [ANSI_PADDING] -- Controls padding of fixed-length variables.
, GET_BIT(@@OPTIONS, 5) /* 32 */ AS [ANSI_NULLS] -- Controls NULL handling when using equality operators.
, GET_BIT(@@OPTIONS, 6) /* 64 */ AS [ARITHABORT] -- Terminates a query when an overflow or divide-by-zero error occurs during query execution.
, GET_BIT(@@OPTIONS, 7) /* 128 */ AS [ARITHIGNORE] -- Returns NULL when an overflow or divide-by-zero error occurs during a query.
, GET_BIT(@@OPTIONS, 8) /* 256 */ AS [QUOTED_IDENTIFIER] -- Differentiates between single and double quotation marks when evaluating an expression.
, GET_BIT(@@OPTIONS, 9) /* 512 */ AS [NOCOUNT] -- Turns off the message returned at the end of each statement that states how many rows were affected.
, GET_BIT(@@OPTIONS, 10) /* 1024 */ AS [ANSI_NULL_DFLT_ON] -- Alters the session's behavior to use ANSI compatibility for nullability. New columns defined without explicit nullability are defined to allow nulls.
, GET_BIT(@@OPTIONS, 11) /* 2048 */ AS [ANSI_NULL_DFLT_OFF] -- Alters the session's behavior not to use ANSI compatibility for nullability. New columns defined without explicit nullability do not allow nulls.
, GET_BIT(@@OPTIONS, 12) /* 4096 */ AS [CONCAT_NULL_YIELDS_NULL] -- Returns NULL when concatenating a NULL value with a string.
, GET_BIT(@@OPTIONS, 13) /* 8192 */ AS [NUMERIC_ROUNDABORT] -- Generates an error when a loss of precision occurs in an expression.
, GET_BIT(@@OPTIONS, 14) /* 16384 */ AS [XACT_ABORT] -- Rolls back a transaction if a Transact-SQL statement raises a run-time error.*/
, GET_BIT(@@OPTIONS, 15) /* 16384 */ AS [XACT_ABORT] -- Rolls back a transaction if a Transact-SQL statement raises a run-time error.*/
GO
Some common SET options are available as part of querying the @@OPTIONS function, the problem is the @@Options returns a bit mask which means it requires some decoding to understand which properties are set or unset. The below query shows the way to get and decode the data
For example, my set options is 5496 which when converted to Binary is 101010111100, remember the binary data needs to be reversed in order to map it with the above column list.
A big challenge with using @@Option is it doesn’t show some available options such as date format which we can see in the DMV above. These are set at the Session level so they account for very specific use cases.
In Summary when you want the OPTIONS list for SQL Server try and use the DMV where possible since it gives the maximum number of details and requires hardly any effort or additional coding.
Links
There are more , please watch the playlist for the complete list.