Home > SQL Server > SQL SERVER – ANSI_DEFAULTS – ANSI Settings in SQL Server – SET Statement

SQL SERVER – ANSI_DEFAULTS – ANSI Settings in SQL Server – SET Statement


SQL Server provides a set of ANSI set statements that helps us to configure the current session with ISO standard behavior. We can choose only specific settings that are necessary for our application. Because, each application requires different settings to process user data. For instance, if we are processing huge volume of numeric data than we may need to set up the environment to take care of all fractional values that are produced by the arithmetic operations. Because, we cannot allow numeric truncation in a scientific calculation.

These are the ANSI (ISO) SET statements available in SQL Server

In this blog post, we are going to learn ANSI_DEFAULTS set statement. This statement controls a collection of SET statements. They are:

  1. SET ANSI_NULLS
  2. SET CURSOR_CLOSE_ON_COMMIT
  3. SET ANSI_NULL_DFLT_ON
  4. SET IMPLICIT_TRANSACTIONS
  5. SET ANSI_PADDING
  6. SET QUOTED_IDENTIFIER
  7. SET ANSI_WARNINGS

We can also set the ANSI_DEFAULTS setting using Query option graphically.

Step 1: Open New Query window and Right click anywhere on the query window.

pic1

Step 2: Now, Click ANSI node on the left hand side of the query option window

pic2

ANSI_DEFAULTS is the server settings and it enables some set statements by default and whenever user creates a session, these set statement are applied to that session automatically. But, use can alter these settings by turning a specific SET statement on or off.

We can use DBCC USEROPTIONS command to see the user options for the current session.

pic3

@@OPTIONS – is a SQL Server function that is used to get the status of all SET statements in the current session.

ANSI_DEFAULTS

Lets see how the ANSI_DEFAULTS set statement affects the current session.

Usage

SET ANSI_DEFAULTS  ON  
or
SET ANSI_DEFAULTS OFF
 

When we set this statement to ON then it enables the following SET statements

SET ANSI_NULLS
SET CURSOR_CLOSE_ON_COMMIT
SET ANSI_NULL_DFLT_ON
SET IMPLICIT_TRANSACTIONS
SET ANSI_PADDING
SET QUOTED_IDENTIFIER
SET ANSI_WARNINGS
 

Example

pic4

If the ANSI_DEFAULTS is set to OFF then

pic5

Note: when we are working with indexes on computed columns and indexed views these SET option must be set to ON (ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, and QUOTED_IDENTIFIER).

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: