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.

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.

See Also

Concepts

Using Options in SQL Server

Other Resources

SET (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance