@@OPTIONS (Transact-SQL)

**APPLIES TO:** ![yes](media/yes.png)SQL Server (starting with 2008) ![yes](media/yes.png)Azure SQL Database ![no](media/no.png)Azure SQL Data Warehouse ![no](media/no.png)Parallel Data Warehouse

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 a table 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 at Configure the user options Server Configuration Option. 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 right most characters (binary 1, 2, and 4) are 0, indicating that the first three items in the table are off. Consulting the table, you see that those are DISABLE_DEF_CNST_CHK and IMPLICIT_TRANSACTIONS, and CURSOR_CLOSE_ON_COMMIT. The next item (ANSI_WARNINGS in the 1000 position) is on. Continue working left though the bit map, and down in the list of options. When the left-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)