SET ANSI_DEFAULTS

Controls a group of Microsoft® SQL Server™ settings that collectively specify some SQL-92 standard behavior.

Syntax

SET ANSI_DEFAULTS { ON | OFF }

Remarks

When enabled (ON), this option enables the following SQL-92 settings:

Together, these SQL-92 standard SET options define the query processing environment for the duration of the user's work session, a running trigger, or a stored procedure. These SET options, however, do not include all of the options required to conform to the SQL-92 standard.

When dealing with indexes on computed columns and indexed views, four of these defaults (ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, and QUOTED_IDENTIFIER) must be set to ON. These defaults are among seven SET options that must be assigned required values when creating and manipulating indexes on computed columns and indexed views. The other SET options are: ARITHABORT (ON), CONCAT_NULL_YIELDS_NULL (ON), and NUMERIC_ROUNDABORT (OFF). For more information about required SET option settings with indexed views and indexes on computed columns, see Considerations When Using SET Statements in SET.

The SQL Server ODBC driver and Microsoft OLE DB Provider for SQL Server automatically set ANSI_DEFAULTS to ON when connecting. The driver and Provider then set CURSOR_CLOSE_ON_COMMIT and IMPLICIT_TRANSACTIONS to OFF. The OFF settings for SET CURSOR_CLOSE_ON_COMMIT and SET IMPLICIT_TRANSACTIONS can be configured in ODBC data sources, in ODBC connection attributes, or in OLE DB connection properties that are set in the application before connecting to SQL Server. SET ANSI_DEFAULTS defaults to OFF for connections from DB-Library applications.

When SET ANSI_DEFAULTS is issued, SET QUOTED_IDENTIFIER is set at parse time, and these options are set at execute time:

Permissions

SET ANSI_DEFAULTS permissions default to all users.

Examples

This example sets SET ANSI_DEFAULTS ON and uses the DBCC USEROPTIONS statement to display the settings that are affected.

-- SET ANSI_DEFAULTS ON.
SET ANSI_DEFAULTS ON
GO
-- Display the current settings.
DBCC USEROPTIONS
GO
-- SET ANSI_DEFAULTS OFF.
SET ANSI_DEFAULTS OFF
GO

See Also

DBCC USEROPTIONS

SET

SET ANSI_NULL_DFLT_ON

SET ANSI_NULLS

SET ANSI_PADDING

SET ANSI_WARNINGS

SET CURSOR_CLOSE_ON_COMMIT

SET IMPLICIT_TRANSACTIONS

SET QUOTED_IDENTIFIER