SET ANSI_DEFAULTS (Transact-SQL)

Controls a group of SQL Server settings that collectively specify some ISO standard behavior.

Topic link iconTransact-SQL Syntax Conventions

Syntax

SET ANSI_DEFAULTS { ON | OFF }

Remarks

SET ANSI_DEFAULTS is a server-side setting that the client does not modify. The client manages its own settings. By default, these settings are the opposite of the server setting. Users should not modify the server setting. To change client the behavior, users should use the SQL_COPT_SS_PRESERVE_CURSORS. For more information, see SQLSetConnectAttr.

When enabled (ON), this option enables the following ISO settings:

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

 

Together, these ISO standard SET options define the query processing environment for the duration of the work session of the user, a running trigger, or a stored procedure. However, these SET options do not include all the options required to comply with the ISO 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 the required values when you are creating and changing 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 the required SET option settings with indexed views and indexes on computed columns, see "Considerations When You Use the SET Statements" in SET (Transact-SQL).

The SQL Server Native Client ODBC driver and SQL Server Native Client 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. The default for SET ANSI_DEFAULTS is OFF for connections from DB-Library applications.

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

SET ANSI_NULLS

SET ANSI_WARNINGS

SET ANSI_NULL_DFLT_ON

SET CURSOR_CLOSE_ON_COMMIT

SET ANSI_PADDING

SET IMPLICIT_TRANSACTIONS

Permissions

Requires membership in the public role.

Examples

The following 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