SET Options

The following table provides an alphabetical list of SET options and the corresponding database and server options that are supported in Microsoft SQL Server 2005.

SET option	Database option	Server option	Default setting
SET ANSI_DEFAULTS (Transact-SQL)	None	None	Not applicable
SET ANSI_NULL_DFLT_OFF (Transact-SQL) SET ANSI_NULL_DFLT_ON (Transact-SQL)	ANSI_NULL_DEFAULT	user options assigns a default	OFF
SET ANSI_NULLS (Transact-SQL)	ANSI_NULLS	user options assigns a default	OFF
SET ANSI_PADDING (Transact-SQL)	ANSI_PADDING	user options assigns a default	ON
SET ANSI_WARNINGS (Transact-SQL)	ANSI_WARNINGS	user options assigns a default	OFF
SET ARITHABORT (Transact-SQL)	ARITHABORT	user options assigns a default	OFF
SET ARITHIGNORE (Transact-SQL)	None	user options assigns a default	OFF
SET CONCAT_NULL_YIELDS_NULL (Transact-SQL)	CONCAT_NULL_YIELDS_NULL	None	OFF
SET CONTEXT_INFO (Transact-SQL)	None	None	OFF
SET CURSOR_CLOSE_ON_COMMIT (Transact-SQL)	CURSOR_CLOSE ON_COMMIT	user options assigns a default	OFF
SET DATEFIRST (Transact-SQL)	None	None	7
SET DATEFORMAT (Transact-SQL)	None	None	mdy
SET DEADLOCK_PRIORITY (Transact-SQL)	None	None	NORMAL
SET FIPS_FLAGGER (Transact-SQL)	None	None	OFF
SET FMTONLY (Transact-SQL)	None	None	OFF
SET FORCEPLAN (Transact-SQL)	None	None	OFF
SET IDENTITY_INSERT (Transact-SQL)	None		OFF
SET IMPLICIT_TRANSACTIONS (Transact-SQL)	None	user options assigns a default	OFF
SET LANGUAGE (Transact-SQL)	None	None	us_english
SET LOCK_TIMEOUT (Transact-SQL)	None	None	No limit
SET NOCOUNT (Transact-SQL)	None	user options assigns a default	OFF
SET NOEXEC (Transact-SQL)	None	None	OFF
SET NUMERIC_ROUNDABORT (Transact-SQL)	NUMERIC_ROUNDABORT	None	OFF
SET OFFSETS (Transact-SQL)	None	None	OFF
SET PARSEONLY (Transact-SQL)	None	None	OFF
SET QUERY_GOVERNOR_COST_LIMIT (Transact-SQL)	None	query governor cost limit	OFF
SET QUOTED_IDENTIFIER (Transact-SQL)	quoted identifier	user options assigns a default	OFF
SET REMOTE_PROC_TRANSACTIONS (Transact-SQL)	None	None	OFF
SET ROWCOUNT (Transact-SQL)	None	None	OFF
SET SHOWPLAN_ALL (Transact-SQL)	None	None	OFF
SET SHOWPLAN_TEXT (Transact-SQL)	None	None	OFF
SET SHOWPLAN_XML (Transact-SQL)	None	None	OFF
SET STATISTICS IO (Transact-SQL)	None	None	OFF
SET STATISTICS PROFILE (Transact-SQL)	None	None	OFF
SET STATISTICS TIME (Transact-SQL)	None	None	OFF
SET STATISTICS XML (Transact-SQL)	None	None	OFF
SET TEXTSIZE (Transact-SQL)	None	None	OFF
SET TRANSACTION ISOLATION LEVEL (Transact-SQL)	None	None	n/a
SET XACT_ABORT (Transact-SQL)	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 the 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 until the user's session with the server is terminated.

• SET options that are set within a stored procedure or trigger apply until they are reset inside that stored procedure or trigger, or until control returns to the code that invoked the stored procedure or trigger.

• A MARS-enabled connection maintains a list of default SET option values. When a batch executes under that connection, the default SET option values are copied to the request's environment. After the batch ends, the environment is copied back to the session's default. In this way, multiple batches executing at the same time under the same connection run under an isolated SET options environment.

  Note

  Under MARS-enabled connections, if multiple batches execute at the same time and they modify the batch execution environment, the resulting default environment for the connection depends on the last batch that has completed execution.

• Unless explicitly reset, SET option values from all higher level code apply within a stored procedure or trigger.

• SET options that are set by a user within a dynamic SQL batch apply only for the duration of that batch.

• Unless explicitly or implicitly reset, SET options that are set for a connection apply after connecting to a different database.

  Note

  An additional consideration is when a user connects to a database and some option may be automatically set to ON, 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 that is set after the shortcut is used will override the corresponding value set by the shortcut.

Note

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

See Also

Concepts

Using Options in SQL Server

Other Resources

SET (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance