@@OPTIONS (Transact-SQL)

  • Article

Returns information about the current SET options.

Topic link icon Transact-SQL Syntax Conventions

Syntax

@@OPTIONS

Return Types

integer

Remarks

The options can come from use of the SET command or from the sp_configure user options value. Session values configured with the SET command override the sp_configure options. Many tools (such as Management Studio automatically configure set options. Each user has an @@OPTIONS function that represents the configuration.

You can change the language and query-processing options for a specific user session by using the SET statement. @@OPTIONS can only detect the options which are set to ON or OFF.

The @@OPTIONS function returns a bitmap of the options, converted to a base 10 (decimal) integer. The bit settings are stored in the locations described in the topic Configure the user options Server Configuration Option.

To decode the @@OPTIONS value, convert the integer returned by @@OPTIONS to binary, and then look up the values on the table above. For example, if SELECT @@OPTIONS; returns the value 5496, use the Windows programmer calculator (calc.exe) to convert decimal 5496 to binary. The result is 1010101111000. The left most characters (binary 1, 2, and 4) are 0, indicating that IMPLICIT_TRANSACTIONS and CURSOR_CLOSE_ON_COMMIT are off. The next item (ANSI_WARNINGS in the 1000 position) is on. Continue working right though the bit map, and down in the list of options. When the right-most options are 0, they are truncated by the type conversion. The bit map 1010101111000 is actually 001010101111000 to represent all 15 options.

Examples

A. Demonstration of how changes affect behavior

The following example demonstrates the difference in concatenation behavior with two different setting of the CONCAT_NULL_YIELDS_NULL option.

SELECT @@OPTIONS AS OriginalOptionsValue;
SET CONCAT_NULL_YIELDS_NULL OFF;
SELECT 'abc' + NULL AS ResultWhen_OFF, @@OPTIONS AS OptionsValueWhen_OFF;

SET CONCAT_NULL_YIELDS_NULL ON;
SELECT 'abc' + NULL AS ResultWhen_ON, @@OPTIONS AS OptionsValueWhen_ON;

B. Testing a client NOCOUNT setting

The following example sets NOCOUNT ON and then tests the value of @@OPTIONS. The NOCOUNT ON option prevents the message about the number of rows affected from being sent back to the requesting client for every statement in a session. The value of @@OPTIONS is set to 512 (0x0200). This represents the NOCOUNT option. This example tests whether the NOCOUNT option is enabled on the client. For example, it can help track performance differences on a client.

SET NOCOUNT ON
IF @@OPTIONS & 512 > 0 
RAISERROR ('Current user has SET NOCOUNT turned on.', 1, 1)

See Also

Reference

Configuration Functions (Transact-SQL)

sp_configure (Transact-SQL)

Concepts

Configure the user options Server Configuration Option