SET OPTIONS (Transact-SQL) - SQL Server Documentation

The SET OPTIONS statement in Transact-SQL (T-SQL) allows you to modify session or query settings that affect the behavior of the SQL Server instance.

Syntax

The general syntax for SET OPTIONS varies depending on the specific option being set. Most options follow this pattern:

SET option_name { ON | OFF | value } [ ,...n ]

Description

SET OPTIONS affects the current session or the execution of a specific query. Understanding these options is crucial for controlling data processing, error handling, and the interpretation of query results.

Common SET Options:

ANSI_NULLS

Controls whether the comparison of NULL values returns TRUE or FALSE.

SET ANSI_NULLS { ON | OFF }

When ANSI_NULLS ON, comparisons with a NULL value evaluate to UNKNOWN (which is treated like FALSE in a WHERE clause). When ANSI_NULLS OFF, comparisons with a NULL value using the equals operator (=) or not equals operator (!= or <>) return TRUE if both operands are NULL, and FALSE otherwise.

QUOTED_IDENTIFIER

Specifies whether double quotes can be used to delimit identifiers (like table and column names) and literal strings.

SET QUOTED_IDENTIFIER { ON | OFF }

It is highly recommended to keep QUOTED_IDENTIFIER ON. This is the default setting for new connections.

NUMERIC_ROUNDABORT

Controls whether an error is raised when a calculation results in a loss of precision or overflow.

SET NUMERIC_ROUNDABORT { ON | OFF }

When NUMERIC_ROUNDABORT ON, an error is raised when the precision of a calculation is not precise enough to store the result. When NUMERIC_ROUNDABORT OFF, any loss of precision or overflow is simply truncated without raising an error.

Statistics IO and Statistics Time

These options are used for performance tuning by providing detailed information about I/O operations and query execution time.

SET STATISTICS IO { ON | OFF }
SET STATISTICS TIME { ON | OFF }

When enabled, SQL Server prints statistics about disk I/O and CPU time after the execution of a statement.

Example Usage:

Setting multiple options for a session:

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET NOCOUNT ON; -- Prevents the sending of DONE_IN_PROC messages

SELECT column_name
FROM table_name
WHERE column_name IS NOT NULL;