@@OPTIONS — Configuration Function

What is @@OPTIONS ?

Returns information about the current SET options

Why it is used?

SQL Server offers many built in Configuration functions and one of these functions, @@OPTIONS which allows you to get the current values that are set for the current session.  When each connection is made the default values are established for each connection and remain set unless they are overridden by some other process

here are the list of set options

Value Configuration Description
1 DISABLE_DEF_CNST_CHK Controls interim or deferred constraint checking.
2 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.
4 CURSOR_CLOSE_ON_COMMIT Controls behavior of cursors after a commit operation has been performed.
8 ANSI_WARNINGS Controls truncation and NULL in aggregate warnings.
16 ANSI_PADDING Controls padding of fixed-length variables.
32 ANSI_NULLS Controls NULL handling when using equality operators.
64 ARITHABORT Terminates a query when an overflow or divide-by-zero error occurs during query execution.
128 ARITHIGNORE Returns NULL when an overflow or divide-by-zero error occurs during a query.
256 QUOTED_IDENTIFIER Differentiates between single and double quotation marks when evaluating an expression.
512 NOCOUNT Turns off the message returned at the end of each statement that states how many rows were affected.
1024 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.
2048 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.
4096 CONCAT_NULL_YIELDS_NULL Returns NULL when concatenating a NULL value with a string.
8192 NUMERIC_ROUNDABORT Generates an error when a loss of precision occurs in an expression.
16384 XACT_ABORT Rolls back a transaction if a Transact-SQL statement raises a run-time error.

Source : Books online SQL server “Denali”

Use the user options option to specify global defaults for all users. A list of default query processing options is established for the duration of a user’s work session.

The user options option allows you to change the default values of the SET options (if the server’s default settings are not appropriate).

A user can override these defaults by using the SET statement. You can configure user options dynamically for new logins. After you change the setting of user options, new login sessions use the new setting; current login sessions are not affected.

The bit positions in user options are identical to those in @@OPTIONS. Each connection has its own @@OPTIONS function, which represents the configuration environment. When logging in to Microsoft SQL Server, a user receives a default environment that assigns the current user options value to @@OPTIONS. Executing SET statements for user options affects the corresponding value in the session’s @@OPTIONS function.

All connections created after this setting is changed receive the new value.

lets get in to the details how this setting can be changed and effect of SQL server performance with these options

SELECT @@OPTIONS AS OPTIONS

This returns a integer and this represents the bit values for each of the options mentioned above

use the below query to know more about the current sessions setting for current user

DECLARE @OPTIONS INT
SELECT @OPTIONS = @@OPTIONS

PRINT @OPTIONS
IF ( (1 & @OPTIONS) = 1 ) PRINT ‘DISABLE_DEF_CNST_CHK’
IF ( (2 & @OPTIONS) = 2 ) PRINT ‘IMPLICIT_TRANSACTIONS’
IF ( (4 & @OPTIONS) = 4 ) PRINT ‘CURSOR_CLOSE_ON_COMMIT’
IF ( (8 & @OPTIONS) = 8 ) PRINT ‘ANSI_WARNINGS’
IF ( (16 & @OPTIONS) = 16 ) PRINT ‘ANSI_PADDING’
IF ( (32 & @OPTIONS) = 32 ) PRINT ‘ANSI_NULLS’
IF ( (64 & @OPTIONS) = 64 ) PRINT ‘ARITHABORT’
IF ( (128 & @OPTIONS) = 128 ) PRINT ‘ARITHIGNORE’
IF ( (256 & @OPTIONS) = 256 ) PRINT ‘QUOTED_IDENTIFIER’
IF ( (512 & @OPTIONS) = 512 ) PRINT ‘NOCOUNT’
IF ( (1024 & @OPTIONS) = 1024 ) PRINT ‘ANSI_NULL_DFLT_ON’
IF ( (2048 & @OPTIONS) = 2048 ) PRINT ‘ANSI_NULL_DFLT_OFF’
IF ( (4096 & @OPTIONS) = 4096 ) PRINT ‘CONCAT_NULL_YIELDS_NULL’
IF ( (8192 & @OPTIONS) = 8192 ) PRINT ‘NUMERIC_ROUNDABORT’
IF ( (16384 & @OPTIONS) = 16384 ) PRINT ‘XACT_ABORT’

Integer value returned from the @@OPTIONS changes according the options turned on/off

SQL Server Query Execution Plan Performance Problems with user options

Query execution plans are created the first time a stored procedure gets executed, once created the plans are cached and reused unless the query optimizer decides based on several factors (schema changes, statistics changes, etc.), to create a new plan. However, different query plans can be created for different connections if they have different SEToptions (SET ARITHABORT ON/OFF, SET ANSI_NULLS ON/OFF, etc.), so it is possible to have several execution plans for the same stored procedure at the same time.

If the user’s application has different SET options than those of the tools you are using to execute the queries (for example, SQL Server Management Studio), you will very likely get a different execution plan then the one the user is getting, and the stored procedure’s performance will be different.

In order to reproduce the problem in the same database where the performance problem is happening, you have to get the SET options that the application’s connection is using and then set your connection the same way. The easiest way to get this information is by using SQL Profiler and tracing the ExistingConnection event class (it is included in theStandard Template).

Once you get the application’s SET options, you can setup your connection the same way and you will be able to get the same cached plans that the users are getting from the application. The following steps show you how to do this:

  1. Launch SQL Profiler
  2. In SQL Profiler, select the Standard Template.
  3. Start the trace with the default settings. The very first rows that will appear correspond to the ExistingConnection event class. Each row corresponds to a different database connection.
  4. Select the row that corresponds to the connection being used by the user’s application and copy the TextData column to your query window. The text data column for the ExistingConnection event class contains the requiredSET commands to setup any connection the same way as the selected connection.
  5. Execute the user’s stored procedure to reproduce the problem. Now you will get the same execution plan that the user is getting and you can start to analyze the execution plan.

@@DATEFIRST – Configuration function

What is  @@DATEFIRST

it is one of the configuration functions returns first day of the week (tinyint) . The U.S. English default is 7, Sunday

Syntax:

SELECT @@DATEFIRST

  • The language setting affects the interpretation of character strings as they are converted to date values for storage in the database, and the display of date values that are stored in the database.
  • This setting does not affect the storage format of date data.
Where it is used ?

Is an integer that indicates the first day of the week. It can be one of the following values.

Value First day of the week is
1 Monday
2 Tuesday
3 Wednesday
4 Thursday
5 Friday
6 Saturday
7 (default, U.S. English) Sunday
It may be confusing   how can  Saturday and Sunday have the same value !
well lets explain that little bit more,  according to the above datefirst value Sunday is considered as first day of the week so SUNDAY -1,MONDAY-2,……..SATURDAY-7
As we just mentioned value of the @@datefirst varies according to the language setting
Lets change the value of  the DATEFIRST  and see what happens when we use the DATEPART
THANK YOU