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