SET Options

This table contains an alphabetic list of SET options and the corresponding database and server options that are supported in Microsoft® SQL Server™.

SET option	Database option	Server option	Default setting
ANSI_NULL_DFLT_ON ANSI_NULL_DFLT_OFF	ANSI null default	user options assigns a default	OFF
ANSI_NULLS	ANSI nulls	user options assigns a default	OFF
ANSI_PADDING	None	user options assigns a default	ON
ANSI_WARNINGS	ANSI warnings	user options assigns a default	OFF
CONCAT_NULL_YIELDS_NULL	concat null yields null	None	OFF
CURSOR_CLOSE_ON _COMMIT	cursor close on commit	user options assigns a default	OFF
IMPLICIT_TRANSACTIONS	None	user options assigns a default	OFF
QUOTED_IDENTIFIER	quoted identifier	user options assigns a default	OFF
ANSI_DEFAULTS	None	None	n/a
ARITHABORT	None	user options assigns a default	OFF
ARITHIGNORE	None	user options assigns a default	OFF
DATEFIRST	None	None	7
DATEFORMAT	None	None	mdy
DEADLOCK_PRIORITY	None	None	NORMAL
DISABLE_DEF_CNST_CHK	None	user options assigns a default	OFF
FIPS_FLAGGER	None	None	OFF
FMTONLY	None	None	OFF
FORCEPLAN	None	None	OFF
IDENTITY_INSERT	None		OFF
LANGUAGE	None	None	us_english
LOCK_TIMEOUT	None	None	No limit
NOCOUNT	None	user options assigns a default	OFF
NOEXEC	None	None	OFF
NUMERIC_ROUNDABORT	None	None	OFF
OFFSETS	None	None	OFF
PARSEONLY	None	None	OFF
PROCID	None	None	OFF
QUERY_GOVERNOR_COST_LIMIT	None	query governor cost limit	OFF
REMOTE_PROC_TRANSACTIONS	None	None	OFF
ROWCOUNT	None	None	OFF
SHOWPLAN_ALL	None	None	OFF
SHOWPLAN_TEXT	None	None	OFF
STATISTICS IO	None	None	OFF
STATISTICS_PROFILE	None	None	n/a
STATISTICS TIME	None	None	OFF
TEXTSIZE	None	None	OFF
TRANSACTION ISOLATION LEVEL	None	None	n/a
XACT_ABORT	None	None	OFF

Parse-time and Execute-time SET Options

The point at which a SET option takes effect depends upon whether the option is a parse-time option or an execute-time option. Parse-time options take effect during parsing, as the options are encountered in text, without regard to control of flow statements. Execute-time options take effect during the execution of the code in which they are specified. If execution fails before the SET statement is executed, the option is not set. If execution fails after the SET statement is executed, the option is set.

The QUOTED_IDENTIFIER, PARSEONLY, OFFSETS, and FIPS_FLAGGER options are parse-time options. All other SET options are execute-time options.

SET QUOTED_IDENTIFIER and SET ANSI_NULLS statements that occur within a batch or stored procedure do not affect that batch or stored procedure. Instead, the settings that are used for statements inside the batch or stored procedure are the settings that are in effect when the batch or stored procedure is created.

Duration of SET Options

This section describes the duration of SET options.

SET options that are set by a user in a script apply until reset or the user's session with the server is terminated.
SET options that are set within a stored procedure or trigger apply until reset inside that stored procedure or trigger or until control returns to the code that invoked the stored procedure or trigger.
Unless explicitly reset, SET option values from all higher level code apply within a stored procedure or trigger.
Unless explicitly or implicitly reset, SET options that have been set for a connection apply after connecting to a different database.

Note An additional consideration is that when a user connects to a database, some option may be set ON automatically, based on the values specified by the prior use of the user options server option or the values that apply to all ODBC and OLE DB connections.

Shortcut SET Option

Transact-SQL provides the SET ANSI_DEFAULTS statement as a shortcut for setting these SQL-92 standard options:

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

The shortcut resets the values for these options. Any individual option set after the shortcut is used overrides the corresponding value set by the shortcut.

Note SET ANSI_DEFAULTS does not set all of the options required to comply with the SQL-92 standard.