Sets SQL Server query-processing options for the duration of the user's work session or for the duration of a running trigger or a stored procedure.
For additional syntax information for the SET statement, see the Microsoft SQL Server Transact-SQL Reference.
The SET statement provides these options.
| Options | Description |
|---|---|
| ANSI_DEFAULTS | Sets options that control ANSI-compliant behavior. |
| ANSI_NULLS | Controls NULL handling by using equality operators. |
| ANSI_PADDING | Controls padding of variables. |
| ANSI_WARNINGS | Controls truncation and NULL in aggregate warnings. |
| CURSOR_CLOSE_ON_COMMIT | Controls behavior of cursors once a commit has been performed. |
| DISABLE_DEF_CNST_CHK | Controls interim constraint checking. |
| FIPS_FLAGGER level | Specifies the level of ANSI compliance that triggers a warning message. The level parameter can be ENTRY, INTERMEDIATE, FULL, or OFF. |
| IMPLICIT_TRANSACTIONS | Controls whether a transaction is started implicitly when a statement is executed. |
| NUMERIC_ROUNDABORT | Controls transaction behavior once a loss-of-precision condition has occurred. |
| REMOTE_PROC_TRANSACTIONS | When enabled, specifies that if a user-defined transaction is active, an MS DTC transaction will start if a remote stored procedure is executed. |
| ROWCOUNT | Accepts int, tinyint, and smallint variables for the parameter that sets the number of rows to be returned in the results set. |
| XACT_ABORT | Controls whether a full transaction termination occurs when an error condition is raised. |
Option syntax:
SET {
ANSI_DEFAULTS {ON | OFF}
| ANSI_NULLS {ON | OFF}
| ANSI_PADDING {ON | OFF}
| ANSI_WARNINGS {ON | OFF}
| CURSOR_CLOSE_ON_COMMIT {ON | OFF}
| DISABLE_DEF_CNST_CHK {ON | OFF}
| FIPS_FLAGGER {level | OFF}
| IMPLICIT_TRANSACTIONS {ON | OFF}
| NUMERIC_ROUNDABORT {ON | OFF}
| REMOTE_PROC_TRANSACTIONS {ON | OFF}
| ROWCOUNT {number | @int_variable}
| XACT_ABORT{ON | OFF}}
where
When enabled, this option enables all the ANSI options, including:
| ANSI_NULLS | ARITHABORT |
| ANSI_NULL_DFLT_ON | CURSOR_CLOSE_ON_COMMIT |
| ANSI_PADDING | IMPLICIT_TRANSACTIONS |
| ANSI_WARNINGS | QUOTED_IDENTIFIER |
All of these ANSI-standard SET options define the query processing environment for the duration of the user's work session, a running trigger, or a stored procedure.
When ANSI_NULLS is enabled, the equal and not equal comparison operators exhibit new behavior. ANSI standards require that a null behaves like a null value in any mathematical context. That is, NULL in any statement causes the statement to evaluate to NULL.
Once you enable the ANSI_NULLS option, you must use the comparison operators IS NULL and IS NOT NULL to compare for a null value.
When ANSI_NULLS is enabled, the SELECT statement syntax will not accept NULL as an argument. For example, if ANSI_NULLS is enabled, this statement does not work if NULL is passed as the department parameter:
CREATE PROCEDURE get_employees (@department CHAR(30)) AS SELECT * FROM department WHERE department.name=@department
This procedure returns rows where the department column matches the @department parameter. When ANSI_NULLS is enabled, the procedure must be rewritten for the instances where the @department parameter is a null value, as follows:
CREATE PROCEDURE get_employees (@department CHAR(30)) AS IF (@department IS NULL) SELECT * FROM department WHERE department.name IS NULL ELSE SELECT * FROM department WHERE department.name=@department
When enabled, the ANSI_PADDING causes varchar and varbinary values to be padded with spaces or nulls. It also affects fixed-length datatypes such as char or int. If the user specifies that columns of fixed-length datatypes (char, int, tinyint, smallint) can be NULL, ANSI_PADDING causes these columns to be padded in the event of a null value.
Note If a table is created while ANSI_PADDING is enabled, the table column will exhibit the padding behavior. Disabling ANSI_PADDING has no effect on existing columns.
For more information about controlling ANSI_WARNINGS, see sp_configure.
An interim violation occurs when constraints are being violated during data modifications. Sometimes these violations are temporary and will no longer exist by the time the data modification concludes. This option suspends reporting of these temporary violations.
This option also can enhance performance. In rare cases, users cannot modify multiple rows because the query runs out of work tables. Work tables are used by the system to resolve suspect constraint violations while processing data modifications. By turning ON the DISABLE_DEF_CNST_CHK option, you can turn off interim constraint checking, which in turn prevents the query from generating interim work tables.
Once DISABLE_DEF_CNST_CHK is set, its new setting is used for the duration of the user's work session or until its value is changed.
The level must be ENTRY, INTERMEDIATE, or FULL as described in the following table.
| Level | Description |
|---|---|
| ENTRY | Standards checking for ANSI SQL entry-level compliance. |
| INTERMEDIATE | Standards checking for ANSI SQL intermediate-level compliance. |
| FULL | Standards checking for full ANSI SQL compliance. |
If checking is turned OFF, no standards checking occurs.
Setting IMPLICIT_TRANSACTIONS opens an implicit transaction when the following statements are used:
| FETCH | ALTER TABLE |
| DELETE | INSERT |
| CREATE | OPEN |
| GRANT | REVOKE |
| DROP | TRUNCATE TABLE |
| SELECT | UPDATE |
When this option is turned on and if there are no outstanding transactions already, every ANSI SQL statement will automatically start a transaction. If there is an open transaction, no new transaction will be started. This transaction has to be explicitly committed by the user by using the command COMMIT TRANSACTION for the changes to take effect and locks released.
Loss of precision occurs when dividing or multiplying. It can also occur in other operations, such as addition and subtraction when the number is too large or too small to represent.
Setting NUMERIC_ROUNDABORT ON terminates any transaction that causes a loss of precision. When it is set to OFF, numeric data is rounded and the transaction continues normally.
Setting XACT_ABORT ON terminates the transaction if any error is encountered. If one statement raises an error, the entire transaction is terminated and rolled back.
Setting XACT_ABORT option is set to OFF terminates only the SQL statement that raised the error. The transaction will continue.
Using the SET statement inside a trigger or stored procedure causes the option to revert to its previous setting after a trigger or stored procedure is executed.
Options changed with the SET statement take effect at the end of the batch. You can combine SET statements and queries in the same batch, but the SET options won't apply to queries in the batch.
Many of these SET options define the query processing environment for the duration of the user's work session or for the duration of a running trigger or stored procedure.
You can also set these options by using sp_configure 'user options'. For information about using sp_configure 'user options', see sp_configure.
For information about viewing these option settings, see @@OPTIONS Global Variable.
SET ANSI_WARNINGS ON