SQL Server Documentation

T-SQL SET Options

The SET statement in Transact-SQL (T-SQL) is used to change the runtime environment of the current session. These options control various aspects of how SQL Server processes commands, handles data, and returns results. Understanding and correctly configuring SET options is crucial for ensuring consistent and predictable query behavior, especially across different environments or when working with legacy code.

Commonly Used SET Options

Here are some of the most frequently encountered and important SET options:

ANSI_NULLS

SET ANSI_NULLS ON/OFF

Determines whether comparisons with NULL are treated according to the ANSI/ISO SQL standard. When ON, an equality comparison with NULL always returns UNKNOWN (which behaves like FALSE in a WHERE clause). When OFF, equality comparison with NULL returns TRUE if both operands are NULL.

ANSI_PADDING

SET ANSI_PADDING ON/OFF

Controls how trailing blanks in character strings are treated. When ON, trailing blanks are preserved. When OFF, trailing blanks are trimmed. This affects comparisons and storage for character data types.

ANSI_WARNINGS

SET ANSI_WARNINGS ON/OFF

Controls whether SQL Server raises warnings for conditions that do not meet ANSI/ISO SQL standards. For example, when ON, it generates a warning if data is truncated during an INSERT or UPDATE, or if a division-by-zero error occurs.

ARITHABORT

SET ARITHABORT ON/OFF

If ON, SQL Server terminates a query if it encounters an arithmetic overflow or divide-by-zero error during expression evaluation. If OFF, the query might continue, returning NULL or zero as appropriate, which can lead to unexpected results.

CONCAT_NULL_YIELDS_NULL

SET CONCAT_NULL_YIELDS_NULL ON/OFF

Controls the behavior of string concatenation with a NULL value. When ON, concatenating a NULL value results in NULL. When OFF, concatenating a NULL value treats it as an empty string, so the result is the non-NULL string.

QUOTED_IDENTIFIER

SET QUOTED_IDENTIFIER ON/OFF

When ON, quoted identifiers (like table or column names enclosed in double quotes) are treated according to the ANSI standard. When OFF, double quotes can be used to enclose character strings, which can conflict with object naming.

NUMERIC_ROUNDABORT

SET NUMERIC_ROUNDABORT ON/OFF

When ON, an error is generated when a precision loss occurs in an expression due to rounding. When OFF, any precision loss due to rounding is ignored, and the expression continues to be evaluated. This option is crucial for financial calculations.

SHOWPLAN_ALL / SHOWPLAN_TEXT / SHOWPLAN_XML

SET SHOWPLAN_ALL ON, SET SHOWPLAN_TEXT ON, SET SHOWPLAN_XML ON

These options instruct SQL Server to return execution plan information instead of the actual result set for the next statement executed. This is invaluable for performance tuning.

ROWCOUNT

SET ROWCOUNT <number>

Limits the number of rows processed by a statement. For example, SET ROWCOUNT 10 will cause the next SELECT, INSERT, UPDATE, or DELETE statement to affect only the first 10 rows.

TEXTSIZE

SET TEXTSIZE <bytes>

Specifies the maximum size, in bytes, of a text, ntext, or image value that is returned by SELECT statements. Default is 4096 bytes.

Managing SET Options

It's good practice to set options at the beginning of a script or stored procedure to ensure a predictable execution environment. You can also view the current settings for your session using the DBCC USEROPTIONS command.

-- Example: Setting multiple options for a script
SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET QUOTED_IDENTIFIER ON;
SET NUMERIC_ROUNDABORT OFF;
SET ROWCOUNT 0; -- Reset ROWCOUNT to process all rows

-- View current session options
DBCC USEROPTIONS;

-- Example of how ANSI_NULLS affects a query
SELECT 1 WHERE NULL = NULL; -- Returns nothing if ANSI_NULLS is ON
SELECT 1 WHERE NULL = NULL; -- Returns 1 if ANSI_NULLS is OFF

Default SET Options

The default values for SET options can vary slightly depending on the SQL Server version and configuration. However, many are set to ON by default to adhere to ANSI/ISO standards. It is always recommended to explicitly set the options you rely on.

Impact of SET Options

Different SET options can significantly alter query results and performance. For instance:

  • ANSI_NULLS and CONCAT_NULL_YIELDS_NULL affect how NULL values are handled in comparisons and concatenations.
  • ARITHABORT and NUMERIC_ROUNDABORT can cause queries to fail or produce different results if arithmetic errors occur.
  • ROWCOUNT is a powerful tool for limiting data processing, useful in scenarios like pagination or quick testing.

Always be mindful of the SET options in effect when writing, debugging, or executing T-SQL code.