Logo 
Search:

SQL Server Articles

Submit Article
Home » Articles » SQL Server » Configuration FunctionsRSS Feeds

@@Options - Options Function

Posted By: Sarita Patel     Category: SQL Server     Views: 3353

This article explains about @@Options function in sql server with examples.

@@OPTIONS function returns information about current set options. Set options can be modified as a whole by using sp_configure user options configuration option. Each user has an @@OPTIONS function that represents the configurations. When user does first time login, it has been assigned a default configuration set by the system administrator. You can change language and query-processing options by using the SET statement.


Syntax of @@OPTIONS Function :

@@OPTIONS

Return type of @@OPTIONS function is int.



Examples of @@OPTIONS Function :

Example 1 : Use of @@OPTIONS function in select clause

SET QUOTED_IDENTIFIER OFF

IF @@OPTIONS & 256 > 0
PRINT 'Single quotation and double quotation marks are differentiated, when evaluating an expression.'
ELSE
PRINT 'Single quotation and double quotation marks are not differentiated, when evaluating an expression.'


Output
Single quotation and double quotation marks are not differentiated, when evaluating an expression.

Above example sets QUOTED_IDENTIFIER and then tests the value of @@OPTIONS. QUOTED_IDENTIFIER option differentiates between single and double quotation while evaluating an expression, when it is ON.

You can change other user options available in sql server are listed below.

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 per formed.

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.

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.

  
Share: 


Didn't find what you were looking for? Find more on @@Options - Options Function Or get search suggestion and latest updates.

Sarita Patel
Sarita Patel author of @@Options - Options Function is from United States.
 
View All Articles

Related Articles and Code:


 
Please enter your Comment

  • Comment should be atleast 30 Characters.
  • Please put code inside [Code] your code [/Code].

 
No Comment Found, Be the First to post comment!