SET ANSI_DEFAULTS (Transact-SQL)
Applies to:
SQL Server
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
SQL analytics endpoint in Microsoft Fabric
Warehouse in Microsoft Fabric
Controls a group of SQL Server settings that collectively specify some ISO standard behavior.
Transact-SQL syntax conventions
Syntax
Syntax for SQL Server, serverless SQL pool in Azure Synapse Analytics, Microsoft Fabric
SET ANSI_DEFAULTS { ON | OFF }
Syntax for Azure Synapse Analytics and Analytics Platform System (PDW)
SET ANSI_DEFAULTS ON
Note
To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.
Remarks
ANSI_DEFAULTS is a server-side setting which can enable the behavior for all client connections. The client typically requests the setting on connection or session initialization. Users should not modify the server setting.
To change client the behavior, users should use the client specific methods like 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, filtered indexes, 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, filtered indexes, 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, filtered indexes, and indexes on computed columns, see Considerations When You Use the SET Statements.
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 CURSOR_CLOSE_ON_COMMIT
and 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 ANSI_DEFAULTS
is OFF for connections from DB-Library applications.
When SET ANSI_DEFAULTS is issued, 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 ANSI_DEFAULTS to 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 (Transact-SQL)
SET Statements (Transact-SQL)
SET ANSI_NULL_DFLT_ON (Transact-SQL)
SET ANSI_NULLS (Transact-SQL)
SET ANSI_PADDING (Transact-SQL)
SET ANSI_WARNINGS (Transact-SQL)
SET CURSOR_CLOSE_ON_COMMIT (Transact-SQL)
SET IMPLICIT_TRANSACTIONS (Transact-SQL)
SET QUOTED_IDENTIFIER (Transact-SQL)