SET Options
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.