Export (0) Print
Expand All

SET Options

SQL Server 2000

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


SET option
Database option Server option Default setting
ANSI_DEFAULTS None None n/a
ANSI_NULL_DFLT_OFF
ANSI_NULL_DFLT_ON
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
ARITHABORT None user options assigns a default OFF
ARITHIGNORE None user options assigns a default OFF
CONCAT_NULL_YIELDS_NULL concat null yields null None OFF
CONTEXT_INFO None None OFF
CURSOR_CLOSE_ON_COMMIT cursor close on commit 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
SET FORCEPLAN None None OFF
IDENTITY_INSERT None   OFF
IMPLICIT_TRANSACTIONS None user options assigns a default 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
QUERY_GOVERNOR_COST_LIMIT None query governor cost limit OFF
QUOTED_IDENTIFIER quoted identifier user options assigns a default 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 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.

Show:
© 2014 Microsoft