user options Option

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. user options 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 logins use the new setting; current logins are not affected.

Value Configuration Description
1 DISABLE_DEF_CNST_CHK Controls interim or deferred constraint checking.
2 IMPLICIT_TRANSACTIONS Controls whether a transaction is started implicitly when a statement is executed.
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 are defined not to allow nulls.


Note Not all configuration values for user options are compatible with each other. For example, ANSI_NULL_DFLT_ON cannot be enabled when ANSI_NULL_DFLT_OFF is enabled.


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 the @@OPTIONS. Executing SET statements for user options affects the corresponding value in the session’s @@OPTIONS.

The setting takes effect immediately (without a server stop and restart).

To configure user options

         

See Also
RECONFIGURE sp_configure
Setting Configuration Options  

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.